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

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?

-

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

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

-
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