Help

Re: Dateadd formulas with multiple IF conditions

Solved
Jump to Solution
652 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Gabrielle_Stich
4 - Data Explorer
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
Justin_Barrett
18 - Pluto
18 - Pluto

@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
Martin_Kopischk
7 - App Architect
7 - App Architect

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

Justin_Barrett
18 - Pluto
18 - Pluto

@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.