What formula can I use to calculate years/months/days of service?

Jes Saylor

Hi there experts-- I'm currently using this formula to calculate employee length of service, but it is adding the months and years together (so the total years are too high):

=ROUND(ROUND((NETDAYS([Hire Date]@row, Today@row) / 365) * 12) / 12) + "yrs " + MOD(ROUND((NETDAYS([Hire Date]@row, Today@row) / 365) * 12), 12) + "mo"

Is there a formula I can use that will show 1 yr 10 mo or 1 yr 9 mo 19days? Either would work as long as it isn't adding the extra year in.

Thanks!

Amanda Alv

Hi,

Here are two options; the first using the average number of days per month, the second by calculating the number of days in a year. I didn't use Netdays as that doesn't take into consideration weekends or holidays and may skew your number a bit.

=ROUND((((TODAY() - [Hire Date]@row) / 30.437)) / 12) + "yrs " + MOD(ROUND((((TODAY() - [Hire Date]@row) / 30.437)) / 12), 12) + "mo"

=ROUND((((TODAY() - [Hire Date]@row) / 365))) + "yrs " + MOD(ROUND((((TODAY() - [Hire Date]@row) / 365))), 12) + "mo"

Hope that helps!