Populate next Monday
HI! My question is similar to this one: https://community.smartsheet.com/discussion/comment/81401#Comment_81401
I have two date columns (let's call them Date1 and Date2). If the date in the Date1 column is a Tuesday-Sunday I want the date in Date2 to populate with the next Monday.
e.g., if Date1 = Tuesday Feb 18 then Date2= Monday Feb 24-- but if Date1 = Monday Feb 17 then Date2= Monday Feb 17.
Hopefully that makes sense. I tried breaking down the formula in the link above but I cannot figure out how to make it the next Monday.
I am able to do this:
=IF(ISDATE(Date1@row), IF(WEEKDAY(Date1@row) = 2, Date1@row + 0, Date1@row - WEEKDAY(Date1@row) + 9))
and it works all the way until I hit a Sunday-- then it adds an extra 8 days (so populates the next, next Monday).
Best Answer
-
Take a look at THIS thread. It has a formula for populating the current Monday based on the day of the week in the Date column. You could adapt this by simply adding 7 to each of the outputs.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Take a look at THIS thread. It has a formula for populating the current Monday based on the day of the week in the Date column. You could adapt this by simply adding 7 to each of the outputs.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome you always come through for me!
Here is the final formula for anyone else:
=Date@row + IF(WEEKDAY(Date@row) = 1, 1, IF(WEEKDAY(Date@row) = 2, 0, IF(WEEKDAY(Date@row) = 3, 6, IF(WEEKDAY(Date@row) = 4, 5, IF(WEEKDAY(Date@row) = 5, 4, IF(WEEKDAY(Date@row) = 6, 3, IF(WEEKDAY(Date@row) = 7, 2)))))))
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!