Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Options
a_gunslinger
edited 12/09/19 in Archived 2016 Posts

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?

 

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭
    edited 12/20/16
    Options

    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
    JamesR ✭✭✭✭✭✭
    Options

    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
    Rob Hagan ✭✭✭
    Options

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

  • a_gunslinger
    Options

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

  • a_gunslinger
    edited 12/22/16
    Options

    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

This discussion has been closed.