Help

Re: Date Formula Help!

Solved
Jump to Solution
863 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Lauren_Madfis
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto
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.

See Solution in Thread

8 Replies 8

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.

image

If you have any curly quotes around the world ‘months’, you also need to replace them with straight quotes.

Thank you, that worked! However, the formula is showing time which I do not need:
image

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)

I got this error when I tried formatting, do I need to add a DATETIME_FORMAT element to the formula?
image

kuovonne
18 - Pluto
18 - Pluto
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.