Date Question
 

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 the
first Monday of the month is denoted by a 1 in column B. 5/8/2006 is
the 2nd Monday of the month so it would have a 2 in column B.

Is there a formula for this, thanks in advance


--
scwilly
------------------------------------------------------------------------
scwilly's Profile: http://www.excelforum.com/member.php...o&userid=18251
View this thread: http://www.excelforum.com/showthread...hreadid=536263

Date Question
 

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

"scwilly" wrote in
message ...

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 the
first Monday of the month is denoted by a 1 in column B. 5/8/2006 is
the 2nd Monday of the month so it would have a 2 in column B.

Is there a formula for this, thanks in advance


--
scwilly
------------------------------------------------------------------------
scwilly's Profile:
http://www.excelforum.com/member.php...o&userid=18251
View this thread: http://www.excelforum.com/showthread...hreadid=536263

Date Question
 

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
--
AP

"scwilly" a écrit
dans le message de
...

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 the
first Monday of the month is denoted by a 1 in column B. 5/8/2006 is
the 2nd Monday of the month so it would have a 2 in column B.

Is there a formula for this, thanks in advance


--
scwilly
------------------------------------------------------------------------
scwilly's Profile:
http://www.excelforum.com/member.php...o&userid=18251
View this thread: http://www.excelforum.com/showthread...hreadid=536263

Date Question
 

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

HTH
--
AP

"Ardus Petus" a écrit dans le message de
...
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
--
AP

"scwilly" a écrit
dans le message de
...

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 the
first Monday of the month is denoted by a 1 in column B. 5/8/2006 is
the 2nd Monday of the month so it would have a 2 in column B.

Is there a formula for this, thanks in advance


--
scwilly
------------------------------------------------------------------------
scwilly's Profile:
http://www.excelforum.com/member.php...o&userid=18251
View this thread:
http://www.excelforum.com/showthread...hreadid=536263