Years of experience - simple formula needed

Options

I need a simple way to calculate years of service using the upcoming date of 7/1/23 and the date of hire as start date.

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Cathy Betzer The most simple way to do it:

    =(DATE(2023, 7, 1) - [Date of Hire]@row) / 365

    That will give you number of whole years in the integer portion of the answer, and a fraction of the whole year in the decimal portion.

    With a Hire Date of 8/1/2016, the above gives you 6.91781. But that's not exactly what you want, is it?

    If you multiply the decimal portion by 365, you get the number of days represented by that decimal:

    .91781 * 365 = 335

    But you can skip the need to isolate the decimal portion for multiplication by using the MOD function:

    =MOD((DATE(2023, 7, 1) - [Date of Hire]@row), 365)

    which returns 335.

    Divide 335 days by 30 days per month, you get 11.16667. So taking the integer portion of this, you could say this employee has been with the company for approximately 6 years, 11 months.

    Tie it all together, using the INT function to just grab the integer portion of each calculation result:

    =INT((DATE(2023, 7, 1) - [Date of Hire]@row) / 365) + " years, " + INT(MOD((DATE(2023, 7, 1) - [Date of Hire]@row), 365) / 30) + " months"

    And to have it only run on rows where there's a date value in [Date of Hire]:

    =IF(ISDATE([Date of Hire]@row), (INT((DATE(2023, 7, 1) - [Date of Hire]@row) / 365) + " years, " + INT(MOD((DATE(2023, 7, 1) - [Date of Hire]@row), 365) / 30) + " months"), "")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Cathy Betzer
    Cathy Betzer ✭✭✭✭✭
    Options

    Thank you. It appears that this is a complex formula. I did get something to work. Thank you for helping.