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

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

Tags:

Answers

  • Paul McGuinness
    Paul McGuinness Overachievers

    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
    PeggyLang ✭✭✭✭✭✭

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

  • Paul McGuinness
    Paul McGuinness Overachievers

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!