Force calculated date to change to first following Monday if not already a Monday
I have a few columns where the dates are based on a formula. It is imperative that some of the date column MUST be a Monday but due to the formula some of the dates do not fall on a Monday. How do I force the date if not currently a Monday to be the next following Monday?
Answers
-
Hi @PeggyLang
This should do the trick for you, rolls everything after Monday to the following Monday
=IF(WEEKDAY(TODAY()) = 2, TODAY(), IF(WEEKDAY(TODAY()) = 3, TODAY() +6, IF(WEEKDAY(TODAY()) = 4, TODAY() +5, IF(WEEKDAY(TODAY()) = 5, TODAY() +4, IF(WEEKDAY(TODAY()) = 6, TODAY() +3, IF(WEEKDAY(TODAY()) = 7, TODAY() +2, IF(WEEKDAY(TODAY()) = 1, TODAY() +1, "Error")))))))
Hope it helps
Thanks
Paul
-
@Paul McGuinness THANK YOU!!! How about another CAVEAT, If the Monday is a holiday can we roll back to the Friday?
-
That should be possible but you would need to have a sheet containing holiday dates for the formula to reference.
You would then use a IF formula before the one above so that it looks for todays date in that data set. if found, the outcome would be Today()-3, otherwise it would then run the formula above.
Apologies but without knowing more around the holiday dataset its difficult to know how best to run the comparison.
Hope that helps
Thanks
Paul
-
@Paul McGuinness I have managed to create a solution to force a monday if the calculated date falls on anything else. Part One DONE & DUSTED. WITH MANY THANKS.
Now I'm stuck on the 'is this date a holiday'? I have put together a worksheet listing all the holidays.
I can't figure out how i'm going to analyze [test date] to determine if it is a holiday - which it actually is.
Maybe i've just been thinking too hard and the answer is too obvious for me at the moment? something like I can't see the forest for the trees??
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!