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
Check out the Formula Handbook template!