first value of the month from a list of dates - problem

  • Thread starter Thread starter peterim
  • Start date Start date
P

peterim

Hello gurus,

I've read through a list of message thread, and you guys are wonderfu
! . Experts !.

Anyway, I hope somebody can help me with this problem.

I have a list of share prices together with the dates. (about 20 to 2
dates in a month).

How do i extract out the first day of every month from a list o
database of 3 years (day by day) ?

I think hard, but no avail. Help...
 
Hi
one way:
- add a helper colum adjacent to your data list (lets say column C)
- in C1 enter the formula: =DAY(A1) ->if column A stores your dates
- copy down for all rows
- Now you can filter by this columns (choose '1' as criteria)
- copy this filtered list to a different location/range
 
Peter,

A bit light on details here<g>.

I have a monthl by year sheet that does something similar, with monthly
columns, daily rows. Getting the first day of month is simply a matter of
getting the first no-blank row.

Give us some data details, and we should be able to be more specific.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hello gurus,

I've read through a list of message thread, and you guys are wonderful
! . Experts !.

Anyway, I hope somebody can help me with this problem.

I have a list of share prices together with the dates. (about 20 to 22
dates in a month).

How do i extract out the first day of every month from a list of
database of 3 years (day by day) ?

I think hard, but no avail. Help....
Click to expand...

Assumptions:

1. Column A has the list of dates in ascending order.
2. Column B has the associated share prices.
3. You have a maximum of 1000 entries.
--------------------
In Column C place a date in the month of interest.

The *array-entered* formula:

=INDEX($A$1:$A$1000,MATCH(1,(YEAR(C1)=YEAR($A$1:$A$1000))*
(MONTH(C1)=MONTH($A$1:$A$1000)),0))

will return the earliest entered date in that month.

The *array-entered* formula:

=INDEX($B$1:$B$1000,MATCH(1,(YEAR(C1)=YEAR($A$1:$A$1000))*
(MONTH(C1)=MONTH($A$1:$A$1000)),0))

will return the associated share price.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

If you have more than 1000 entries, adjust the $1000 factors in the various
formulas accordingly.


--ron
 
If the first day of the month isn't always 1, then you can compare the month
to the previous month

In b2 for example

=if(month(A1)<>Month(B2,"First Day","")

then drag fill down.
 
Hello again gurus,

I've tried, but still no avail . :-(

The values of the first date of the month are not same, I would like t
"export" the results to a new sheet, that stores the first day of th
month.

I've included the sample code.

Sheet1 contains the dates , and the share prices.

I would like to "automatically" put the values in

Sheet2 so that it stores the first day of the month.


Lots of thanks to Tom Ogilvy, Ron Rosenfeld, Bob Phillips and Fran
Kabel for their generiosity. :-

Attachment filename: equity.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44357
 
Peter,

Give this a try

=INDEX(B1:B45,MIN(IF(MONTH(A4:A45)=9,ROW(A4:A45))),1)

where = 9 refers to the month. It's an array formula, so commit with
Ctrl-SHift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Help... still cannot make it!

I've managed to get the first day of the month, by comparing the mont
with the previous one.

Now, i would like to copy all the first day of the month to a new shee
(without the blanks").

How can i do it ? Is there any command ?

Here's my worksheet for easier understanding

Attachment filename: equity.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44459
 
Thanks Ron,

I've finally got it!. Thanks to your great formula !. By the way
what's your occupation? :-
 
Thanks Ron,

I've finally got it!. Thanks to your great formula !. By the way,
what's your occupation? :-)
Click to expand...

Thank you for the feedback.

I'm retired and I don't do anything! <g>.


--ron
 
Back
Top