Hello! I am trying to create a formula to help track tenure and extension dates for personnel.
Each person has a “start date” – I want to have formula fields that calculate quarterly extensions +12 months; +15 months; +18 months
Here is the formula I entered: DATEADD({Start Date},+12,‘months’) but I am getting an error
Best answer by kuovonne
IF({Date Start}, DATEADD({Date Start}, 12, 'months'))
You don’t need the empty strings (""). The first one is unnecessary because if there is a date at all, it is considered a “truthy” value. The second empty string as the third parameter of the IF forces the returned result to be a string, which is causing the strange formatting.
Hi @Lauren_Madfis
remove the + from your formula and it should work. You are already declaring that you are going to add to the date, you don’t need to say it again.
Hi @Lauren_Madfis
remove the + from your formula and it should work. You are already declaring that you are going to add to the date, you don’t need to say it again.
@Vivid-Squid I tried that and still got an error! I tired adding spaces, removing them, etc but keep getting an error.
It looks like you still have the + in your formula
DATEADD({Date Start}, 12, 'months')
You should only get an error if there is no date in the Date Start field.
+17@Vivid-Squid I tried that and still got an error! I tired adding spaces, removing them, etc but keep getting an error.
If you have any curly quotes around the world ‘months’, you also need to replace them with straight quotes.
It looks like you still have the + in your formula
DATEADD({Date Start}, 12, 'months')
You should only get an error if there is no date in the Date Start field.
Thank you, that worked! However, the formula is showing time which I do not need:
Also, I know there is an IF function I can add to the formula so I don’t return an #ERROR if there is no date in the “start date” field.
Hi,
You can hide the time, by opening the formula editor window and clicking on Formatting.
You can add an IF to not ERROR if there is no date. Something like
IF({Date Start} != "", DATEADD({Date Start}, 12, 'months'), "")
This reads out as: IF the field called Date Start ({Date Start}) is not (or does not equal blank != “”) exclamation point, sometimes called a bang, in front of equals means not equals. Then an empty string as two quotes “”. Now when that statement is true, do our DATEADD in the second comma section. And in the last comma section we tell it what to do if our first statement is false so we say to display an empty string with two quotes “”.
So an IF is really simple, IF (1 something is true, 2 do this, 3 if not do this)
Hi,
You can hide the time, by opening the formula editor window and clicking on Formatting.
You can add an IF to not ERROR if there is no date. Something like
IF({Date Start} != "", DATEADD({Date Start}, 12, 'months'), "")
This reads out as: IF the field called Date Start ({Date Start}) is not (or does not equal blank != “”) exclamation point, sometimes called a bang, in front of equals means not equals. Then an empty string as two quotes “”. Now when that statement is true, do our DATEADD in the second comma section. And in the last comma section we tell it what to do if our first statement is false so we say to display an empty string with two quotes “”.
So an IF is really simple, IF (1 something is true, 2 do this, 3 if not do this)
I got this error when I tried formatting, do I need to add a DATETIME_FORMAT element to the formula?
+17IF({Date Start}, DATEADD({Date Start}, 12, 'months'))
You don’t need the empty strings (""). The first one is unnecessary because if there is a date at all, it is considered a “truthy” value. The second empty string as the third parameter of the IF forces the returned result to be a string, which is causing the strange formatting.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
