Syncing tables across bases--help needed

[Lauren_Madfis]

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

[Vivid-Squid]

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.

[Lauren_Madfis]

Lauren_Madfis:

DATEADD({Start Date},+12,‘months’)

@Vivid-Squid I tried that and still got an error! I tired adding spaces, removing them, etc but keep getting an error.

[Vivid-Squid]

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.

[kuovonne]

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

[Lauren_Madfis]

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.

[Vivid-Squid]

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)

[Lauren_Madfis]

Vivid-Squid:

IF({Date Start} != "", DATEADD({Date Start}, 12, 'months'), "")

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

[kuovonne · Accepted solution]

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.