Locating first instance of a new month in a column

[John Eppley]

Hi:

I have a column of sequential dates in Column "I". Some dates have multiple
entries. I am trying to identify the first occurrence... of the first day...
of the current month. I know how to manipulate NOW and DATE functions but I
am stymied when trying to find the ROW number for the row containing the
first occurrence of the current month.

I am presently using a group of formulas, example..........
=SUMIF(INDIRECT("J"&M$1&":J"&N$1),L2,INDIRECT("I"&M$1&":J"&N$1))
that requires that I supply a row number in cell (M1). Cell(N1) increases
that value by 100. The formula I am looking for would exist in M1 and
automate the process automatically.

I have checked McRitchie's site along with Pearson's. It is possible that I
may have to write a macro that will autoexecute when I open the file. A
function would be preferred.

Thanks in advance.
John Eppley

 

[Don Guillett]

if m1 contains 7/1/2003 date
=MATCH(M1,A7:A1000)-1
or
=MATCH(TODAY(),A7:A1000)-1
adjust to get the right date

 

[acw]

John

Try
=MATCH(DATEVALUE("1/"&MONTH(NOW()) & "/" & YEAR(NOW
())),I:I,1)

This will find the first appearance of the 1st of the
current month in column I.


Tony

 

[acw]

John

Oops. Should be
=MATCH(DATEVALUE("1/"&MONTH(NOW()) & "/" & YEAR(NOW
())),I:I,0)

Also, this will return the row number of the match.


Tony

 

[John Eppley]

Thanks for the quick reply. I never considered the MATCH function. Thanks
for the tip.

There is still some problems related to the formula. When there are more
than one entry for the first day of the month, the returned "row number" is
for the LAST entry for that date. I tested the results by using a reduced
version of the suggested formula.

=MATCH(DATEVALUE("1-Jul-03"),H:H,1) is being used for test purposes.

Note that the date must be enclosed with quotes. When I try to apply the
suggested formula I get a #NAME? error.

John

 

[John Eppley]

Hi again: I also discovered another small problem. The suggested formula
reduces to a DATEVALUE for Jan-7-2003. Hmmm. Now I am really confused.

Regards, John

 

[John Eppley]

Hi "ACW"

Viola...all is solved !

Reversing the month with date solves the datevalue quandary. Then, replacing
the "1" with "0" gives the FIRST occurrence of the value being searched. My
"NAME" problem was my fault. I copied and pasted your submitted formula, and
somehow a "period" replaced the "comma". Everything works beautifully.

I appreciated your input and patience. Again, thanks for your help.

Regards, John