Complicated reminder formula

[Sean_Hyland1]

Hi everybody,
I need your help, please!
I have a base with an action date, you have sixty days to complete the action. This is the deadline date. The deadline formula looks like this DATEADD({Action Date},60,‘days’)
Now my problem is - If the deadline date is on a weekend, I want the reminder date to show as the next working day ie the Monday. Is there a formula that will accomplish this?
Thanks in advance,

Sean

[kuovonne]

This formula should do the trick.

SWITCH(
    WEEKDAY(DATEADD({Action Date},60,'days')),
    0, DATEADD({Action Date},61,'days'),
    6, DATEADD({Action Date},62,'days'),
    DATEADD({Action Date},60,'days')
)

The logic could have been shortened a bit by checking the weekday of the {Action Date} first, but I think that this way of writing the formula makes the intent more clear.

[Justin_Barrett]

There’s also the WORKDAY() function, which automatically skips weekends, plus you don’t need to indicate “days” because that’s the only interval that the formula supports.

WORKDAY({Action Date}, 60)

However, it doesn’t just avoid weekends at the end of the calculation. It literally returns a date that’s X working days in the future; i.e. skipping all weekends from the start date to the target date (plus an optional list of holidays; see the docs for more). Maybe that’ll work in your case, maybe it won’t, but I thought I’d toss it out there for consideration.

[Sean_Hyland1]

Thanks for getting back with that Justin. Don’t think it will work for me because I want to calculate 60 days (working and non-working) and if the 60th day lands on a weekend day (Sat or Sun) show the date of the following Monday. I know it’s an unusual one.

[Justin_Barrett]

In that case, maybe combine both functions. Add 59 full days via DATEADD(), then add the last day only via WORKDAY():

WORKDAY(DATEADD({Action Date}, 59, "days"), 1)

[Sean_Hyland1]

Hi kuovonne

Took me a while to get my head around your formula but it works perfectly. Thank you so much for your help.

Sean