Function to calculate date of next Monday
Hi all,
I'm trying to add a function in a summary date field that simply notes the date of the next Monday from today. It feels like it should be simple, but I'm struggling to wrap my head around the TODAY function.
Can anyone suggest a formula that might work?
Best Answer
-
Hey @Karen Webber
I may have an approach that will work. Are you looking for a single cell that displays the next Monday on a rolling basis, or are you looking for the next Monday per row, and once that row date is displayed you want that Monday date to be fixed? I ask because the approach to these two scenarios are slightly different.
For the single cell rolling Monday, based off TODAY(), here is a formula. It takes advantage of the WEEKDAY function, and the fact that for WEEKDAY, Monday = 2. This formula must be in a DATE field.
For the first IF, I assumed if Today is Sunday, you want the next week's Monday, not the following day Monday. If it is the following day, the 8 would change to a 1.
=IF(WEEKDAY(TODAY()) < 2, TODAY() + 8, IF(WEEKDAY(TODAY()) >= 2, TODAY() + 9 - WEEKDAY(TODAY())))
Let me know if you need to fix the date instead of keeping it rolling.
Kelly
Answers
-
Hey @Karen Webber
I may have an approach that will work. Are you looking for a single cell that displays the next Monday on a rolling basis, or are you looking for the next Monday per row, and once that row date is displayed you want that Monday date to be fixed? I ask because the approach to these two scenarios are slightly different.
For the single cell rolling Monday, based off TODAY(), here is a formula. It takes advantage of the WEEKDAY function, and the fact that for WEEKDAY, Monday = 2. This formula must be in a DATE field.
For the first IF, I assumed if Today is Sunday, you want the next week's Monday, not the following day Monday. If it is the following day, the 8 would change to a 1.
=IF(WEEKDAY(TODAY()) < 2, TODAY() + 8, IF(WEEKDAY(TODAY()) >= 2, TODAY() + 9 - WEEKDAY(TODAY())))
Let me know if you need to fix the date instead of keeping it rolling.
Kelly
-
Hi @Kelly Moore, this is exactly what I needed, and you're a wizard. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!