Date Question

S

scwilly

I have a list of dates in column A starting with:

5/1/2006
5/2/2006
5/3/2006
5/4/2006
5/5/2006
5/6/2006
5/7/2006
5/8/2006

How can I identify 5/1/2006 as being the first Monday of the month
5/2/2006 being the first Tuesday of the month in column B? Where th
first Monday of the month is denoted by a 1 in column B. 5/8/2006 i
the 2nd Monday of the month so it would have a 2 in column B.

Is there a formula for this, thanks in advanc
 
B

Biff

Hi!

Is this for only a single month?

One way:

You might want to identify the weekday of the date. Maybe in column B:

=A1 and Custom format as dddd

Or:

=TEXT(A1,"dddd")

Then in column C:

=SUMPRODUCT(--(WEEKDAY(A$1:A1,2)=WEEKDAY(A1,2)))

Copy both B1 and C1 down as needed.

Note: this will only work for a one month span.

For something more robust:

=SUMPRODUCT(--(WEEKDAY(A$1:A1,2)=WEEKDAY(A1,2)),--(MONTH(A$1:A1)=MONTH(A1)),--(YEAR(A$1:A1)=YEAR(A1)))

Biff
 
A

Ardus Petus

Simpler:
=IF(WEEKDAY(A1,2)=1,1+INT(DAY(A1)/7),"")

HTH
--
AP

Ardus Petus said:
For numbering Modays, use following formula in B1 and copy down:
=IF(WEEKDAY(A1,2)=1,IF(MONTH(A1-7)<MONTH(A1),1,1+INT(DAY(A1)/7)),"")

HTH
Click to expand...
 
Top