Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date question | Excel Worksheet Functions | |||
Date Question | Excel Discussion (Misc queries) | |||
Date Question | Excel Discussion (Misc queries) | |||
Reference question | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) |