DATE_ADD formula issue

[Patricia_Martin]

Hi, In my table I have a field where I want the expiration date of a contract be automátically calculated based on another date field (when the contract was signed) + 36 months duration period. The formula is as follows:
DATEADD({signed}, 36, “months”)

Most of our contracts are for 3 year periods, but because, we actually have some that are for one year, I thought I could create another number field where I could set as default the value “36” but eventually could change the number of months to “12”, for example. Now the new formula is:
DATEADD({signed}, {contract duration}, “months”)

PROBLEM:
The date appearing in the signed field is June 20, 2015. In the first formula, the date that resulted was June 20, 2018 (signed date, 36 months later)

The second formula however came June 19, 2018, a day earlier.

I’ve tried with different month values. it always comes one day earlier. Any tips, suggestions, etc would be appreciated.

[Justin_Barrett]

What type of field is {contract duration}? If it’s just a number field, that difference shouldn’t be there. If it’s another field type, that might explain the difference, but I can’t say for sure until I know the type.

As a side note, have you thought about calculating the expiration date using “years” instead of “months”? It shouldn’t have any noticeable impact on the end result, but it’s worth a play anyway.

[kuovonne · Accepted solution]

Patricia_Martin:

it always comes one day earlier

Check the field formatting options and make sure that “use same timezone” is set the same for both the original date field and the resulting calculated date.

Formula fields with dates that are sometimes one day off from the expected result are almost always the result of a time zone issue. Date and date/time fields are always stored in GMT time under the hood. Date times that do not have a time, are always set to midnight GMT time when processed in formula fields. If you are east of GMT time, that can make dates one day earlier.

[Justin_Barrett]

I believe that @Patricia_Martin is testing two different formulas: one with a literal number (36), the other with a field reference to retrieve a number. In theory the number is the same in both cases, but she said that the one-day difference is introduced by the formula that’s pulling the number from another field. While it’s possible that the two formulas have different formatting options, I get the feeling that something else is at play here.

[kuovonne]

[quote=“Justin_Barrett, post:2, topic:42437”]
What type of field is {contract duration} ?

Also, if it is a calculated number, what are the formatting settings for the number? It is possible that the calculated field is set to display as an integer, but is actually a decimal under the hood.

On the other hand, I really suspect that it is a timezone issue, and not an issue with the {contract duration} field.

[Patricia_Martin]

It’s a DATE type field Justin. It has to be in months and not in years cause some values are 18 months, for example.

[Patricia_Martin]

Thanks so much kuovonne. I followed your tip and checked “same time zone” and that solved the problem!

[Justin_Barrett]

Patricia_Martin:

It’s a DATE type field Justin.

I think you might have misinterpreted my question. I was asking about {contract duration}, the field where you put the duration value in months.

That aside, I’m glad that it was a simple fix with the field settings! :thumbs_up: