How to Calculate Years of Service in Years and Months?
This article explains how to calculate years of service from an employee’s hire date in the format “X years Y months” in Excel. It covers calculations based on today’s date as well as using a specific reference date.
Calculating Years of Service Until Today Using the TODAY Function
The TODAY function dynamically retrieves the current date, making it useful for calculating years of service up to today. The date updates automatically as days change.
Formula
If the hire date is in cell A2, use the following formula in cell B2 to display years of service:
=DATEDIF(A2, TODAY(), “Y”) & ” years ” & DATEDIF(A2, TODAY(), “YM”) & ” months”
Example
| A | B | |
|---|---|---|
| 1 | Hire Date | Years of Service |
| 2 | 01/04/2015 | =DATEDIF(A2, TODAY(), “Y”) & ” years ” & DATEDIF(A2, TODAY(), “YM”) & ” months” |
Details of the Formula (Using Today’s Date)
- =DATEDIF(A2, TODAY(), “Y”): Calculates the number of years between the hire date (A2) and today (returned by TODAY()).
- =DATEDIF(A2, TODAY(), “YM”): Calculates the number of months between the hire date and today, excluding the years.
- These results are combined with the text “years” and “months” for display in the format “X years Y months”.
Calculating Years of Service for a Specific Reference Date
To calculate years of service up to a specific date (e.g., a fiscal year-end or evaluation date), you can specify the reference date in a separate cell.
Formula
If the hire date is in cell A2 and the reference date is in cell B2, use the following formula in cell C2:
=DATEDIF(A2, B2, “Y”) & ” years ” & DATEDIF(A2, B2, “YM”) & ” months”
Example
| A | B | C | |
|---|---|---|---|
| 1 | Hire Date | Reference Date | Years of Service |
| 2 | 01/04/2015 |
01/04/2023 |
=DATEDIF(A2, B2, “Y”) & ” years ” & DATEDIF(A2, B2, “YM”) & ” months” |
Details of the Formula (Using a Reference Date)
- =DATEDIF(A2, B2, “Y”): Calculates the number of years between the hire date (A2) and the reference date (B2).
- =DATEDIF(A2, B2, “YM”): Calculates the number of months between the hire date and the reference date, excluding the years.
- The results are combined with “years” and “months” to display in the desired format.
Handling “0 Months” in Results
If the result includes “0 months” (e.g., “5 years 0 months”), you can use an IF function to suppress the “0 months” part:
Example:
=DATEDIF(A2, TODAY(), “Y”) & ” years” & IF(DATEDIF(A2, TODAY(), “YM”)=0, “”, ” ” & DATEDIF(A2, TODAY(), “YM”) & ” months”)
This formula omits “months” if the value is 0, displaying only “X years”.
Conclusion
To calculate years of service in the format “X years Y months,” the DATEDIF function is an effective tool. Use the TODAY function for current-date-based calculations, or specify a reference date for custom scenarios. Adapt these formulas to suit your needs and ensure accurate calculations of years of service.