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

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...
 
F

Frank Kabel

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
 
B

Bob Phillips

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)
 
R

Ron Rosenfeld

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
 
T

Tom Ogilvy

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.
 
P

peterim

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
 
B

Bob Phillips

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)
 
P

peterim

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
 
P

peterim

Thanks Ron,

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

Ron Rosenfeld

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
 
Top