Years of experience - simple formula needed

Cathy Betzer

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.

Jeff Reisman

@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"), "")

Cathy Betzer

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