Locating first instance of a new month in a column

  • Thread starter Thread starter John Eppley
  • Start date Start date
J

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
 
if m1 contains 7/1/2003 date
=MATCH(M1,A7:A1000)-1
or
=MATCH(TODAY(),A7:A1000)-1
adjust to get the right date
 
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
 
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
 
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
 
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
 
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
 
Back
Top