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 | Associate Director, Smartsheet
CrossCountry Consulting - 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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!