Formula to calculate the 5th [day of the week] when applicable

Question

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?

Answer 1

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.

Answer 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")