Number of years/month between TODAY and a given date not working

NEw trial user ;^)

Have a very basic calculation. From a Purchase Date I would like to calculate the number of Years and Months that have passed since purchase date from TODAY.

Two fields:

Purchase Date: Date

Age: calculated difference of TODAY() - Purchase Date.

But each forumula comes back #unparaseable. But most work in Excel.

=DATEDIF([Purchase Date]1 - TODAY())

=DATEDIF([Purchase Date]1,TODAY(),"ym")

[Purchase Date]1 - TODAY()

I can get a calculation between two dates, just not from TODAY. But even that comes back in "days" and I would prefer Years, months.

=[Purchase Date]1 - [Retired Date]1 gives me 1460, but to convert that 1460 days to years

=[Purchase Date]1 - [Retired Date]1 /365 gives me unparseable.

Are Smartsheets formulas that much different from excel?

Find more posts tagged with

Comments

Rob Hagan

Morning a_gunslinger,

Formulas in SmartSheet are very similar to those in Excel. A good staring point is to read what SmartSheet has published about all of their formulas (search for "SmartSheet formulas"). There are many date manipulation formulas that are available and they mimic those in Excel.

A date is SmartSheet is a number. Just like Excel, a date is the number of days from an universally agreed starting point. Such a number may be not a whole number, as the time (as a fraction of the 24 hour day) is also included. I have found that it is prudent to use DATEONLY() around each date to ensure that the time component is discarded.

Your years formula (above) needs parentheses as in =([Purchase Date]1 - [Retired Date]1) /365 to override the operator precedence. Personally, I use 365.25 rather than 365 in order to accommodate for leap years to some degree.

As to the unparseable bit, without seeing your actual sheet it is tricky to say why that is happening. If the field names are not spelt correctly, then that can lead to unparseable.

Cheers,

Rob.

JamesR

Your =[Purchase Date]1 - [Retired Date]1 /365

should be =([Purchase Date]1 - [Retired Date]1 )/365.25 to get years

or =(([Purchase Date]1 - [Retired Date]1 )/365.25)30 to get Months approx

Failing that use Year, Month and Day functions to subtract element each of the dates and then concaternate the result back together

Ther are many other ways depending on the accuracy required.

Rob Hagan

Well said, James... I notice that you also use the 365.25 approach.

a_gunslinger

Thanks for all the responses! I will digest them and report back.

a_gunslinger

With your help I was able to make it work. Thank you. I changed it from Purcase Date and Retired date to Purchase Date relative to TODAY using:

=(TODAY() - [Start Date]1) / 365.25