software

Hi: I have several workbooks which contain data sequentially entered by
quot;datequot;. 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 quot;mm-01-yyquot;.
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
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

quot;John Eppleyquot; gt; wrote in message ...
gt; Hi: I have several workbooks which contain data sequentially entered by quot;datequot;. It is necessary to determine the FIRST data
gt; entry for a given month. For example, I may have a data entry for the first of the month quot;mm-01-yyquot;. My equations work well for
gt; 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
gt; the first month's entry.
gt;
gt; John
gt;
gt;
gt;
gt;
gt;

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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=521365In cell B2 enter:
=IF(MONTH(A2)=MONTH(A1),quot;quot;,ROW(A2))

And Copy Down
quot;John Eppleyquot; gt; wrote in message
...
gt; Hi: I have several workbooks which contain data sequentially entered by
gt; quot;datequot;. It is necessary to determine the FIRST data entry for a given
gt; month. For example, I may have a data entry for the first of the month
gt; quot;mm-01-yyquot;. My equations work well for this condition, but if there is NO
gt; data entry for the first of the month I do not know how to accurately find
gt; the row number of the first month's entry.
gt;
gt; John
gt;
gt;
gt;
gt;
gt;
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
quot;=MATCH(DATEVALUE(MONTH(TODAY())amp;quot;-1-quot;amp;YEAR(TODAY())),A:A,0)quot;

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

Thank you.
John
quot;Niek Ottenquot; gt; wrote in message
...
gt; Hi John,
gt;
gt; With dates in A and search date in B1:
gt;
gt; =IF(MONTH(INDEX(A1:A39,MATCH(B1,A1:A39)))=MONTH(B1 ),MATCH(B1,A1:A39),MATCH(B1,A1:A39) 1)
gt;
gt; Of course the formula can be much shorter if you use an extra cell for the
gt;
gt; MATCH(B1,A1:A39)
gt;
gt; part
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;John Eppleyquot; gt; wrote in message
gt; ...
gt;gt; Hi: I have several workbooks which contain data sequentially entered by
gt;gt; quot;datequot;. It is necessary to determine the FIRST data entry for a given
gt;gt; month. For example, I may have a data entry for the first of the month
gt;gt; quot;mm-01-yyquot;. My equations work well for this condition, but if there is NO
gt;gt; data entry for the first of the month I do not know how to accurately
gt;gt; find the row number of the first month's entry.
gt;gt;
gt;gt; John
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
Niek: My apologies.....when cell B1 contains quot;NOW()quot; the result is the last
row#. When I replace cell B1 with quot;6/01/06quot; your formula behaves perfectly.
Thanks for your help.

John
quot;
More problems. Note that the MATCH function has a default of quot;1quot; for the
quot;matchtypequot;. 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 quot;1quot; and there is more than one entry
for that date. It will also fail for a matchtype of quot;0quot; and there is NO
entry for the first day of the month. For the last year I have been using a
quot;placeholderquot; of a ficticious entry for the first-of-the-month. That way, a
matchtype of quot;1quot; will always work.

John Eppley