Force calculated date to change to first following Monday if not already a Monday

PeggyLang

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?

Paul McGuinness

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

PeggyLang

@Paul McGuinness THANK YOU!!! How about another CAVEAT, If the Monday is a holiday can we roll back to the Friday?

Paul McGuinness

@PeggyLang

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

PeggyLang

@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??