Home |
Search |
Today's Posts |
#1
|
|||
|
|||
First Monday
If Jan/02/06 is the first Monday of the month how can I determine the date of
the first Monday in Feb, March, Apr. and so on. I would also like to be able to determine the date for the 2nd Thursday of each month. Thanks Charlie |
#2
|
|||
|
|||
With help from Chip Pearson (http://www.cpearson.com/excel/DateTimeWS.htm)
Enter any date during the month in question in cell A1: =DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),1,0,6,5,4,3,2) gives you the first Monday. =DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),4,3,2,1,0,6,5)+7 Gives you the second Thursday. Hope this helps Rowan "Charlie O'Neill" wrote: If Jan/02/06 is the first Monday of the month how can I determine the date of the first Monday in Feb, March, Apr. and so on. I would also like to be able to determine the date for the 2nd Thursday of each month. Thanks Charlie |
#3
|
|||
|
|||
Enter your date 2-jan-06 in A1,
In B1, enter: =WEEKDAY(A1) A2:A12 enter 2, 3, 4... and so on for each month B2: =DATE(YEAR($A$1),A2,1) C2: =B2-WEEKDAY(B2)+$B$1+IF(WEEKDAY(B2)$B$1,7) and drag down for all the first mondays in each month for 2nd tuesday =B2-WEEKDAY(B2)+$B$1+1+IF(WEEKDAY(B2)$B$1+1,14) Alternatively, you can simply change the date in A1 to get a tuesday and in the cell C2 change 7 to 14 to get 2nd tuesdays. Mangesh "Charlie O'Neill" wrote in message ... If Jan/02/06 is the first Monday of the month how can I determine the date of the first Monday in Feb, March, Apr. and so on. I would also like to be able to determine the date for the 2nd Thursday of each month. Thanks Charlie |
#4
|
|||
|
|||
Stolen from Daniel Maher
=A1+7-WEEKDAY(A1+5) where A1 holds the 1st date of any month so if A1 holds 01/02/2006 and in A2 you want the first Monday in February and A3 March and so on =DATE(YEAR(A1),MONTH(A1)+1,1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)+5) in A2 then copy down for 2nd Thursdays =A1+14-WEEKDAY(A1+2) -- Regards, Peo Sjoblom (No private emails please) "Charlie O'Neill" wrote in message ... If Jan/02/06 is the first Monday of the month how can I determine the date of the first Monday in Feb, March, Apr. and so on. I would also like to be able to determine the date for the 2nd Thursday of each month. Thanks Charlie |
#5
|
|||
|
|||
Generic formula
=DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW)) Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day, Sun=1, Mon=2, etc. -- HTH RP (remove nothere from the email address if mailing direct) "Charlie O'Neill" wrote in message ... If Jan/02/06 is the first Monday of the month how can I determine the date of the first Monday in Feb, March, Apr. and so on. I would also like to be able to determine the date for the 2nd Thursday of each month. Thanks Charlie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nth Day of the Month Formula, (aka, "First Monday")
"Bob Phillips" wrote: Generic formula =DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW)) Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day, Sun=1, Mon=2, etc. -- HTH RP (remove nothere from the email address if mailing direct) "Charlie O'Neill" wrote in message ... If Jan/02/06 is the first Monday of the month how can I determine the date of the first Monday in Feb, March, Apr. and so on. I would also like to be able to determine the date for the 2nd Thursday of each month. Thanks Charlie Generic formula above did not work for me. I received the error NAME#?. DOUG in Wichita |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nth Day of the Month Formula, (aka, "First Monday")
On Thu, 15 Apr 2010 08:26:01 -0700, DOUG
wrote: Generic formula above did not work for me. I received the error NAME#?. DOUG in Wichita Then one of your NAME's, either in the formula or in a precedent, is not properly defined. Possibly it's DOW, but without more information, everything is speculation. --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nth Day of the Month Formula, (aka, "First Monday")
Just to add to Ron's post...
You did actually change the Nth to a real number and the DoW to a real number, right? And are you using an English language version of excel? And are you in A1 reference style or R1C1 reference style? Do you see letters at the top of each column or numbers??? DOUG wrote: "Bob Phillips" wrote: Generic formula =DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW)) Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day, Sun=1, Mon=2, etc. -- HTH RP (remove nothere from the email address if mailing direct) "Charlie O'Neill" wrote in message ... If Jan/02/06 is the first Monday of the month how can I determine the date of the first Monday in Feb, March, Apr. and so on. I would also like to be able to determine the date for the 2nd Thursday of each month. Thanks Charlie Generic formula above did not work for me. I received the error NAME#?. DOUG in Wichita -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nth Day of the Month Formula, (aka, "First Monday")
ps. You may want to post the actual formula that you used, too.
Dave Peterson wrote: Just to add to Ron's post... You did actually change the Nth to a real number and the DoW to a real number, right? And are you using an English language version of excel? And are you in A1 reference style or R1C1 reference style? Do you see letters at the top of each column or numbers??? DOUG wrote: "Bob Phillips" wrote: Generic formula =DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW)) Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day, Sun=1, Mon=2, etc. -- HTH RP (remove nothere from the email address if mailing direct) "Charlie O'Neill" wrote in message ... If Jan/02/06 is the first Monday of the month how can I determine the date of the first Monday in Feb, March, Apr. and so on. I would also like to be able to determine the date for the 2nd Thursday of each month. Thanks Charlie Generic formula above did not work for me. I received the error NAME#?. DOUG in Wichita -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get a footnote to give the date Monday, 4 July 2005 | Excel Discussion (Misc queries) | |||
Microsoft Excel : How can we get the date of the Monday of Week 44 of Year 2005? | Excel Worksheet Functions | |||
Date Calculations | Excel Worksheet Functions | |||
Auto_Open() How to show prior Monday? | Excel Discussion (Misc queries) | |||
Multiple IF factors | Excel Worksheet Functions |