Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

=DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday))

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:

=DATE(B2,A2,1+7*1)-WEEKDAY(DATE(B2,A2,8-7))

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

2 Answers 2

Try this formula

=(B2&"-"&A2&"-"&1+7*n-WEEKDAY(DATE(B2,A2,8-xday)))+0

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

2012-10-30

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

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,
          DATE($B$2,$A$2,1+7*n)-WEEKDAY(DATE($B$2,$A$2,8-xday)),
          "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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.