Help

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
Jump to Solution
426 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

@Gabrielle_Stichweh A couple other notes about your formula design…

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.

See Solution in Thread

2 Replies 2

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

@Gabrielle_Stichweh A couple other notes about your formula design…

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.