How to find the Nth weekday of a given month?

I'm working on a formula that will provide the date of the 3rd Thursday of the month. That much I have working, but am trying to modify it to work for any weekday of any month. As of now the first and last weeks of the month are causing issues (returning a date from previous month, returning a date in the next week, etc.). Here is the formula I have so far:

=IF(AND([Month Week]@row = 1, DAY(TODAY()) <= 7), DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row)) + ([Week Day Calculations]@row - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row) + 7))), IF(AND([Month Week]@row = 2, DAY(TODAY()) <= 14), DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row) + 7) + ([Week Day Calculations]@row - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row) + 14))), IF(AND([Month Week]@row = 3, DAY(TODAY()) <= 21), DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row) + 14) + ([Week Day Calculations]@row - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row) + 21))), IF(AND([Month Week]@row = 4, DAY(TODAY()) <= 28), DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row) + 21) + ([Week Day Calculations]@row - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row) + 28))), ""))))

And a screenshot of the accompanying sheet:

The initial formula is based off of the one by J. Craig Williams at the bottom of this post: https://community.smartsheet.com/discussion/505/calculate-the-xth-weekday-of-any-month

Any help would be greatly appreciated & I would be happy to provide more context if needed.

Find more posts tagged with

Sheets

Formulas

Project Management

Accepted answers

Colin B

Hi Nick,

Thank you for the detailed response. After some troubleshooting we were able to get this formula working:

=IF(ISBLANK([Month Week]@row), "", IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) = [Week Day Calculations]@row, (DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Day]@row)) + (([Month Week]@row - 1) * 7)) + ([Week Day Calculations]@row - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Day]@row) + 7))), IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) < [Week Day Calculations]@row, (DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Day]@row)) + (([Month Week]@row - 1) * 7)) + ([Week Day Calculations]@row - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Day]@row) + 7))), IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) > [Week Day Calculations]@row, (DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row)) + (([Month Week]@row - 1) * 7)) + ([Week Day Calculations]@row - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY([1st Monday]@row) + 7))), ""))))

Instead of doing the IF statement by week, we got it working by using your suggestion of having it check if the 1st of the month is greater/less than or equal to the day we are looking for.

All comments

Nick Korna

Hi @Colin B,

This should work, without needing intervening columns - you can go straight from a Date column to result:

=IF(WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)) = 5, DATE(YEAR(Date@row), MONTH(Date@row), 1) + 14, IF(WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)) < 5, DATE(YEAR(Date@row), MONTH(Date@row), (15 + (5 - WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1))))), DATE(YEAR(Date@row), MONTH(Date@row), 20 + (7 - WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1))))))

It looks what weekday the 1st of the month of the given date is, then calculates from there:

If the 1st is a Thursday, the 3rd Thursday will be 14 days later.

If the 1st is earlier in the week using the WEEKDAY function than Thursday, the 3rd Thursday day date will be (15 + (5-WEEKDAY(1st of the month)). This uses the weekday function which runs Sunday (as 1) to Saturday (as 7).

If the 1st is later in the week using WEEKDAY, then the 3rd Thursday will be on the day of (20+(7-WEEKDAY(1st of the month).

The numbers in the formula can be adapted to find alternatives if necessary, for example the first Monday would use the following:

=IF(WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)) = 2, DATE(YEAR(Date@row), MONTH(Date@row), 1)+0, IF(WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1)) < 2, DATE(YEAR(Date@row), MONTH(Date@row), (1 + (2 - WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1))))), DATE(YEAR(Date@row), MONTH(Date@row), 3 + (7 - WEEKDAY(DATE(YEAR(Date@row), MONTH(Date@row), 1))))))

In the above you can see the bolded numbers are what would need changing. The WEEKDAY portions (the 2s) are straightforward - these are the WEEKDAY value of the day you're after. The 0 would be replaced by (7*number of weeks in future) and so on.

Hope this helps, but if you've any questions then just ask! 😊

Colin B

Hi Nick,

Thank you for the detailed response. After some troubleshooting we were able to get this formula working:

Instead of doing the IF statement by week, we got it working by using your suggestion of having it check if the 1st of the month is greater/less than or equal to the day we are looking for.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.