Excel can be quite useful when you’re working dates. It has powerful built-in formulas that allow you to do calculations based on dates.
One common strategy for performing calculations with dates would be to find out how many years or months or dates have elapsed between two given dates.
It could especially be useful to calculate years of service of employees in your company (where you have their joining date and the end date).
Calculating Years of Service Using the YEARFRAC Formula in excel
If you want to get the total number of years of service between two dates, you can use the YEARFRAC function.
Below I have a data set where I have the start date and end date for a set of workers, and I want to calculate their years of service in column D.
You can do that using the below formula:
- =INT(YEARFRAC(B2,C2,1))
The YEARFRAC function gives you the total number of years between two dates (where full years are integers and incomplete years are decimals).
The INT function then drags the integer part from the result of YERAFRAC, which is the whole number of years of service between the two given dates.
Calculating Years of Service Using the DATEDIF Formula in Excel
If you require to calculate the use of service where you may like to get the number of years as well as the number of months and or days, then you are better off using the DATEDIF formula (pronounced as DATE DIF formula)
It’s one of the undocumented functions in Ms Excel, which means that you would not find any help on this function from Microsoft or see this as a part of the IntelliSense when you type it in a cell in MS Excel.
Calculating Only the Years of Service in excel
Below I have a data set where I keep the start date and end date for a set of workers, and I want to calculate the total number of years of service for each employee.
Below is the formula that will do this:
- =DATEDIF(B2,C2,”y”)
The DATEDIF formula takes the start and the end date as the first two arguments and gives the full years between the dates (as I have specified Y as the third argument).
Calculating Service Duration in Years and Months Excel
Below I again have the same data set, and I want to calculate each employee’s service duration in years and months.
Below is the formula that will do this:
=DATEDIF(B2,C2,"y")&" Years "&DATEDIF(B2,C2,"ym")&" Months"
The above formula contains two DATEDIF functions in Excel
- The first one calculates the total number of completed years between the two dates.
- The second one is used to calculate the total number of months between the two dates but does not include those months completed a year. For example, if the difference between two dates is 14 month, this would only return 2, as the first 12 month completed to become a years.
These DATEDIF functions are combined with the text “Years” and “Months” to make the result more meaningful, and the user would know how many years and months of service has been completed.
Calculating Service Duration in Years, Months, and Days Excel
And if you want to get the total service duration in Years, Months as Days, you can use the down formula Use:
=DATEDIF(B2,C2,”y”)&” Years “&DATEDIF(B2,C2,”ym”)&” Months “&DATEDIF(B2,C2,”md”)&” Days”
The above formula uses three DATEDIF functions to determine the numbers of years, months, and days in the full service and then combine these using the & operator.
