Row # of first "day" of current month.
 

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

Row # of first "day" of current month.
 

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

--
Kind regards,

Niek Otten

"John Eppley" wrote in message ...
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

Row # of first "day" of current month.
 

Perhaps

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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=521365

Row # of first "day" of current month.
 

In cell B2 enter:
=IF(MONTH(A2)=MONTH(A1),"",ROW(A2))

And Copy Down



"John Eppley" wrote in message
...
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

Row # of first "day" of current month.
 

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



"Niek Otten" wrote in message
...
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

--
Kind regards,

Niek Otten

"John Eppley" wrote in message
...
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

Row # of first "day" of current month.
 

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
"

Row # of first "day" of current month.
 

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