Xth weekday of next month formula
Hi,
I am trying to write a formula that will return a date that is the Xth weekday of the month following a date entry.
For example. I have 4/21/23 as my date entry. In a separate cell I want it to show the date for the second Monday of the month of May (5/8/23).
I found this formula but it is based off the current month of the Date I have already:
=IF(WEEKDAY(DATE(YEAR(DateColumn1), MONTH(DateColumn1), 1)) <= 6, 10, 17) + (DateColumn1 - DAY(DateColumn1)) - WEEKDAY(DATE(YEAR(DateColumn1), MONTH(DateColumn1), 1))
Any ideas on how I can adjust my formula?
Answers
-
REplace each instance of
DATE(YEAR(DateColumn1), MONTH(DateColumn1), 1)
with
IFERROR(DATE(YEAR(DateColumn1), MONTH(DateColumn1) + 1, 1), DATE(YEAR(DateColumn1) + 1, 1, 1))
-
This is my new formula but it isn't quite right still. It returns 4/8/23. The second Monday of May is the 8th, but the month that is returned is incorrect. Any suggested edits:
=IF(WEEKDAY(IFERROR(DATE(YEAR(DateColumn1), MONTH(DateColumn1) + 1, 1), DATE(YEAR(DateColumn1) + 1, 1, 1))) <= 6, 10, 17) + (DateColumn1 - DAY(DateColumn1)) - WEEKDAY(IFERROR(DATE(YEAR(DateColumn1), MONTH(DateColumn1) + 1, 1), DATE(YEAR(DateColumn1) + 1, 1, 1)))
-
Will it always be the 2nd Monday of the next month? Or is it always going to be the next Monday in 2 weeks?
Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
It is more helpful if it is the 2nd Monday of the next month. Ultimately I want to adapt the formula so that I can make it the 4th Tuesday of the next month, or the 3rd Wednesday of the next month, or the 1st Thursday of the next month.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!