Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

# Dateadd formulas with multiple IF conditions

Topic Labels: Automations
Solved
426 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

I need to change this formula to ignore weekends. It is successfully calculating the next day a task should take place on based on frequency, but that frequency should add 1 or 2 days if it is on a Sunday or Saturday. Anyone know how to change my formula to account for this?

IF((Frequency = “Monthly”), (DATEADD(Due, 30, ‘days’)),
IF((Frequency = “Biweekly”), (DATEADD(Due, 14, ‘days’)),
IF((Frequency = “Weekly”), (DATEADD(Due, 7, ‘days’)),
IF((Frequency = “Daily”), (DATEADD(Due, 1, ‘days’)), Due))))

1 Solution

Accepted Solutions
18 - Pluto

First off, you have extra parentheses around your conditions and the `DATEADD()` functions. Those can be pulled out, leaving you with this (converting to `WORKDAY()` as @Martin_Kopischke pointed out):

``````IF(Frequency = "Monthly", WORKDAY(Due, 30),
IF(Frequency = "Biweekly", WORKDAY(Due, 14),
IF(Frequency = "Weekly", WORKDAY(Due, 7),
IF(Frequency = "Daily", WORKDAY(Due, 1), Due))))
``````

This could also be simplified further using the `SWITCH()` function. `SWITCH()` is great when you want to switch the output based on what you find via a single input (in your case, the `{Frequency}` field). Converting that formula to use `SWITCH()` looks like this:

``````WORKDAY(
Due, SWITCH(
Frequency,
"Monthly", 30,
"Biweekly", 14,
"Weekly", 7,
"Daily", 1,
0
)
)
``````

In short, this switches the number of days added to the due date based on the value in `{Frequency}`. The final `0` in the `SWITCH()` function is a fallback value in case none of the other options match. If I’m reading your original formula correctly, you just want the due date to remain unmodified if there’s no frequency set, and adding 0 days to it takes care of that.

2 Replies 2
7 - App Architect

That is exactly what the WORKDAY() function is for :winking_face: .

18 - Pluto

First off, you have extra parentheses around your conditions and the `DATEADD()` functions. Those can be pulled out, leaving you with this (converting to `WORKDAY()` as @Martin_Kopischke pointed out):

``````IF(Frequency = "Monthly", WORKDAY(Due, 30),
IF(Frequency = "Biweekly", WORKDAY(Due, 14),
IF(Frequency = "Weekly", WORKDAY(Due, 7),
IF(Frequency = "Daily", WORKDAY(Due, 1), Due))))
``````

This could also be simplified further using the `SWITCH()` function. `SWITCH()` is great when you want to switch the output based on what you find via a single input (in your case, the `{Frequency}` field). Converting that formula to use `SWITCH()` looks like this:

``````WORKDAY(
Due, SWITCH(
Frequency,
"Monthly", 30,
"Biweekly", 14,
"Weekly", 7,
"Daily", 1,
0
)
)
``````

In short, this switches the number of days added to the due date based on the value in `{Frequency}`. The final `0` in the `SWITCH()` function is a fallback value in case none of the other options match. If I’m reading your original formula correctly, you just want the due date to remain unmodified if there’s no frequency set, and adding 0 days to it takes care of that.