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.
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.
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…
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…
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…
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…
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!