Formula for # of Years

McAfee Electric Mgr

Does anyone have a formula to calculate the number of years from a given date? Example

Hire Date : 5/23/2020

Today's Date: 5/23/2022

Years of Service: 2

Thanks!

Toufong Vang

Hire Date: 01/02/2020

Today's Date: 05/23/2022

Years of Service: 2 (rounded to 0 decimal place)

=ROUND(NETDAYS([Hire Date]@row, TODAY())/365,0)

Years of Service: 2.4 (rounded to 1 decimal place)

=ROUND(NETDAYS([Hire Date]@row, TODAY())/365,1)

Explanation

1. Use NETDAYS() to find the number of days elapsed between two dates. NETDAYS([Hire Date]@row, TODAY())
2. Divide the number of days elapsed by 365 to arrive at the number of years. NETDAYS([Hire Date]@row, TODAY())/365
3. Use ROUND() to round to the desired decimal places. ROUND(NETDAYS([Hire Date]@row, TODAY())/365,0)

Documentation on Smartsheet functions can be found at https://help.smartsheet.com/functions

Cheers!

Jeff Reisman

As an alternative to @Toufong Vang 's excellent formula, depending on how close to exact you want to be, you could always just subtract the year values:

=YEAR(TODAY()) - YEAR([Hire Date]@row)

2022 - 2020 = 2