Calculation with Dates - Strange Behaviour?
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?
Best Answer
-
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))
Answers
-
Make sure your [Invoice Sent Date] column is set as a date type column.
EDIT: As well as the [Due Date] column.
-
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)
-
How are the dates in the [Invoice Sent Date] column populated?
-
How are the dates in the [Invoice Sent Date] column populated?
By hand (click on calendar icon in the cell).
-
Ok. How is the data in the [Payment Term (days)] column populated?
-
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.
-
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.
-
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))
-
Thank you Paul!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!