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?

Leila

Posted 2012-10-10T13:56:27.197

Reputation: 1

Answers

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.

Scott

Posted 2012-10-10T13:56:27.197

Reputation: 17 653

Thanks Scott. I can't chance the overflow into the next month. but thanks for answering :-) – Leila – 2012-10-11T20:12:16.183

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 – 2012-10-11T20:29:57.400

@Leila: “I can’t chance the overflow into the next month, but thanks for answering. :-)” What do you mean? – Scott – 2012-10-13T00:02:06.067

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 houdini

Posted 2012-10-10T13:56:27.197

Reputation: 10 434

Barry, this is just what I needed. Thank you so much!!! :-) – Leila – 2012-10-11T20:13:25.590