Great formula - thanks very much. Does in a few characters what I had been trying to do over pages.
How do I take account of the nth day. ie which part of the formula changes with say the third Friday? Also any ideas o how to drag out the last day of a month?
Problem:
Calculating the date of, for example, the first Saturday of each of the months listed in column A, for the year specified in cell B2.
Solution:
Use the WEEKDAY and DATE functions as shown in the following formula:
=DATE($B$2,A2,1+((1-(6>=WEEKDAY(DATE($B$2,A2,1),2)))*7+(6-WEEKDAY(DATE($B$2,A2,1),2))))
Here's a simpler way....
Generically you can get the nth xday of the month with this formula
=DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday))
where year is in B2 and month (as a number 1 to 12) is in A2, and where xday is a number representing the day of the week (1 = Sun through to 7 = Sat), so for 1st Saturday that becomes
=DATE(B2,A2,1+7*1)-WEEKDAY(DATE(B2,A2,8-7))
or for 4th Thursday
=DATE(B2,A2,1+7*4)-WEEKDAY(DATE(B2,A2,8-5))
...so that last formula will give you the date of US Thanksgiving if A2 is 11 and B2 the year in question.
If you want the last Monday in a month, because you may not know whether there are 4 or 5 Mondays in that month you can use the formula to get the first Monday of the following month and subtract 7, e.g. for the last Monday in March 2011 if you have 3 in A2 and 2011 in B2 use
=DATE(B2,A2+1,1+7*1)-WEEKDAY(DATE(B2,A2+1,8-2))-7
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Bookmarks