Row # of first "day" of current month.

[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

[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

--
Kind regards,

Niek Otten

"John Eppley" <[email protected]> wrote in message news:[email protected]...
> 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
>
>
>
>
>

[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

[Jim May]

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

And Copy Down



"John Eppley" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
>

[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



"Niek Otten" <[email protected]> wrote in message
news:u%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>>
>>
>
>

[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
"

[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