Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 31, 2021 10:07 AM
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
Aug 31, 2021 11:38 AM
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.
Aug 31, 2021 02:07 PM
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.
Sep 01, 2021 09:31 AM
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.
Sep 01, 2021 10:02 AM
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)
Sep 01, 2021 10:56 AM
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