# Nest IF Formula - Help with automatic dates for deadlines

#1

Hi,

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

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} = “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},

))))))))

0 Likes

#2

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'),
``````

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.

0 Likes

#3

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…

0 Likes

#4

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…

1 Like

#5

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!

0 Likes