Skip to main content

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))))

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.

Reply


Terms & Conditions