Nest IF Formula - Help with automatic dates for deadlines

[megan_Schnaid]

Hi,

Struggling with this Nested IF formula. Maybe I need to use “Find”?

Goal to automatically calculate deadline based on type of task.
A) If it’s annual, biannual, quarterly or monthly then ADD the respective days to the creation date to produce the deadline.
B) if it’s a One-Time task or “see notes” or as needed, then pull the deadline date from another column

Here is what I have:
IF({Type} = “One-Time”, {Deadline},

IF({Type} = “Annual | Preventative/Recurring”, DATEADD{Date Entered}, 365, ‘days’,

IF({Type} = “Bi-annual | Preventative/Recurring”, DATEADD{Date Entered}, 365, ‘days’,

IF({Type} = “Quarter | Preventative/Recurring”, DATEADD{Date Entered}, 365, ‘days’,

IF({Type} = “Monthly | Preventative/Recurring”, DATEADD{Date Entered}, 365, ‘days’,

IF({Type} = “See Notes | Preventative/Recurring”, {Deadline},

IF({Type} = “As Needed | Preventative/Recurring”, {Deadline},

IF({Type} = “Project”, {Deadline}

))))))))

[Justin_Barrett]

If you want to stick with the nested IF structure, you need to fix a few things. Where you have:

DATEADD{Date Entered}, 365, 'days',

…it should be:

DATEADD({Date Entered}, 365, 'days'),

You’re missing parentheses around the arguments that are being fed to DATEADD.

However, consider reformatting this with the SWITCH function. SWITCH is great for when you’re checking a single item to see if it can be one of several possible values, and the output is similarly simple. In your case, because you have several options that use {Deadline} as the output, you only really need to check for cases when it’s not one of those, with all other values defaulting to {Deadline}. In that light, a SWITCH function would look like this:

SWITCH({Type}, "Annual | Preventative/Recurring", DATEADD({Date Entered}, 1, 'years'),
    "Bi-annual | Preventative/Recurring", DATEADD({Date Entered}, 6, 'months'),
    "Quarter | Preventative/Recurring", DATEADD({Date Entered}, 1, 'quarters'),
    "Monthly | Preventative/Recurring", DATEADD({Date Entered}, 1, 'months'),
    {Deadline})

I also reworked the values being fed to DATEADD to make it easier than counting days every time.

EDIT: fixed curly quotes in the formula.

[megan_Schnaid]

Thank you Justin! While the formula is still showing errors… that I can’t figure out, I am thankful that you showed me the SWITCH function and simplified my DATEADD!

Now… just to get it to work…

[W_Vann_Hall]

Errors in date calculations are often caused by a formula trying to act upon a non-existent value. For instance, if you have records without {Date Entered}, you might want to wrap the entire SWITCH() statement with IF({Date Entered},SWITCH(...)) to keep DATEADD() from throwing an error…

[Justin_Barrett]

The error turned out to be caused by curly quotes, which I inadvertently carried over after copying and modifying the original IF sequence. It’s fixed now!