Row # of first "day" of current month.

J

John Eppley

Hi: I have several workbooks which contain data sequentially entered by
"date". It is necessary to determine the FIRST data entry for a given month.
For example, I may have a data entry for the first of the month "mm-01-yy".
My equations work well for this condition, but if there is NO data entry for
the first of the month I do not know how to accurately find the row number
of the first month's entry.

John
 
N

Niek Otten

Hi John,

With dates in A and search date in B1:

=IF(MONTH(INDEX(A1:A39,MATCH(B1,A1:A39)))=MONTH(B1),MATCH(B1,A1:A39),MATCH(B1,A1:A39)+1)

Of course the formula can be much shorter if you use an extra cell for the

MATCH(B1,A1:A39)

part
 
D

daddylonglegs

Perhaps

=MATCH(TRUE,INDEX(MONTH(A1:A39)=MONTH(B1),0),0)

although you might want to do this differently depending on your
ultimate aim
 
J

John Eppley

Your formula appears to give the row# of the last day of the current month.
I am looking for the row number of the FIRST entry for the current month. I
can easily find the last row# by using COUNT(A:A).

I have been using
"=MATCH(DATEVALUE(MONTH(TODAY())&"-1-"&YEAR(TODAY())),A:A,0)"

This formula fails if there is NO entry for the first day of the month.

Thank you.
John
 
J

John Eppley

Niek: My apologies.....when cell B1 contains "NOW()" the result is the last
row#. When I replace cell B1 with "6/01/06" your formula behaves perfectly.
Thanks for your help.

John
"
 
J

John Eppley

More problems. Note that the MATCH function has a default of "1" for the
"matchtype". The data can have three possibilities regarding the first day
of the month. There can be one entry, there can be NO entries, or there can
be more than one entry.

The formula fails if the matchtype is a "1" and there is more than one entry
for that date. It will also fail for a matchtype of "0" and there is NO
entry for the first day of the month. For the last year I have been using a
"placeholder" of a ficticious entry for the first-of-the-month. That way, a
matchtype of "1" will always work.

John Eppley
 
Top