How to get years of service?

lizzyh

I need help figuring out a formula that calculates the number of years an employee has been with the company. I have their "Date of Hire" in there. I have tried this formula but it comes up as "INVALID DATA TYPE", I can't figure out how to calculate the number of years from their date of hire to today's date.

Paul Newcome

Try...

=YEAR(TODAY()) - YEAR([Date of Hire]@row) - IF(TODAY()< DATE(YEAR(TODAY()), MONTH([Date of Hire]@row), DAY([Date of Hire]@row)), 1, 0)

lizzyh

Thank you! I had to change the column type to "date" and then I used the first part of that formula -- =YEAR(TODAY()) - YEAR([Date of Hire]@row).

Paul Newcome

Yes. The column type definitely needs to be a date type.

The only issue you may run into with only using the first part is that if you look at your screenshot, that row would read as a year even though it has only been 5 months.

Peggy Parchert

@lizzyh @Paul Newcome - I went at this a different way but I really do like what Paul came up with better. Sharing just for your knowledge.

I created a TODAY Helper column that is updated from a workflow automation (record a date). This is the workaround I use so that I don't have to open the file every day to update.

I created a Sheet Summary field: # day in year and entered 365 in the field. I realize some years have 366 but I was good with just going with 365.

The formula in the Years with ATS column is:

=([TODAY Helper]@row - [Date of Hire]@row) / [# days in year]#

Then I made sure that it showed one decimal.

Peggy