Help

Complicated reminder formula

Topic Labels: Formulas
1997 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Sean_Hyland1
5 - Automation Enthusiast
5 - Automation Enthusiast

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

5 Replies 5

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.

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.

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.

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)

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