Calculation with Dates - Strange Behaviour?

Manu

I have the following formula in my Due Date column:

=IF(ISBLANK([Invoice Sent Date]@row), "Invoice not sent", [Invoice Sent Date]@row + [Payment Term (days)]@row)

And when I test with the value 15 in the Payment Term (days) I get this really odd result, adding 15 to the year value... in fact not even adding but replacing what is the year 2020 into the year 2015 ?!

It's even more odd because all I am trying to do really is what is described in this article (https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates) but instead of using hardwired values I use the value from another column.

What am I missing?

Paul Newcome

That is correct. The apostrophe converts it to text instead of an actual number. Lets try using a VALUE function to convert it back into a numerical value...

=IF(ISBLANK([Invoice Sent Date]@row), "Invoice not sent", [Invoice Sent Date]@row + VALUE([Payment Term (days)]@row))

Paul Newcome

Make sure your [Invoice Sent Date] column is set as a date type column.

EDIT: As well as the [Due Date] column.

Manu

Hi Paul

[Invoice Sent Date] and [Due date] already are.

[Payment Term] has to be a number because it is x days after a certain date (the Invoice Sent Date)

Paul Newcome

How are the dates in the [Invoice Sent Date] column populated?

Manu

How are the dates in the [Invoice Sent Date] column populated?

By hand (click on calendar icon in the cell).

Paul Newcome

Ok. How is the data in the [Payment Term (days)] column populated?

Manu

There's the rub - I just had the same thought. They are supplied via the API and I just and overwrote the numbers manually which fixes the problem.... so I will check with my dev how he passes that value and why it may have this effect.

Manu

Hah for some reason when that value is passed via the API it prefixes it with a ' .... but that apostrophe is not visible unless you edit the cell hence why I never saw it.

Paul Newcome

That is correct. The apostrophe converts it to text instead of an actual number. Lets try using a VALUE function to convert it back into a numerical value...

=IF(ISBLANK([Invoice Sent Date]@row), "Invoice not sent", [Invoice Sent Date]@row + VALUE([Payment Term (days)]@row))

Manu

Thank you Paul!

Paul Newcome

Happy to help! 👍️