Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've used the following formula to get the nth xday of the month:


where year is in B2 and month (as a number from 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:


Now, this works for months with 4 weeks. How can I modify this formula to calculate when a month has 5 weeks?

share|improve this question

As a quick and ugly solution, you could say

=IF(MONTH(DATE($B$2,$A$2,1+7*n)-WEEKDAY(DATE($B$2,$A$2,8-xday))) = $A$2,
          "Non-existent date")

I.e., if the date calculated by your formula is in the correct month, use it.  If you have overflowed into the first week of the following month, then don’t use it.

share|improve this answer
Thanks Scott. I can't chance the overflow into the next month. but thanks for answering :-) – Leila Oct 11 '12 at 20:12
I think Scott's solution does the same as mine. If there's no 5th Monday or whatever then you get "Non-existent date"..... – barry houdini Oct 11 '12 at 20:29
@Leila: “I can’t chance the overflow into the next month, but thanks for answering. :-)” What do you mean? – Scott Oct 13 '12 at 0:02

Try this formula


That does effectively the same as your original except it builds the date as a text string, e.g. for 5th Tuesday in October 2012 it will create this text string


....then the +0 at the end co-erces that string to a date, 30-Oct-2012. But if there is no 5th xday then the string created is not a valid date, e.g. for 5th Saturday in October you'd get 2012-10-34. Clearly that isn't a valid date so when you add zero the coercion doesn't work and you get #VALUE! error. If you prefer a text message instead then just use IFERROR, something like this

=IFERROR((B2&"-"&A2&"-"&1+7*n-WEEKDAY(DATE(B2,A2,8-xday)))+0,"No 5th day")

share|improve this answer
Barry, this is just what I needed. Thank you so much!!! :-) – Leila Oct 11 '12 at 20:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .