One common scenario where you have to perform calculations with dates would be to find out how many years or months or dates have elapsed between two given dates. This could especially be useful if you want to calculate years of service of employees in your company (where you have their joining date and the end date). In this tutorial, I will show you some formula techniques that you can use to calculate the years of service in years (or years and months and days).
Calculating Years of Service Using the YEARFRAC Formula
If you only 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 employees, and I want to calculate their years of service in column D. You can do that using the below formula: The YEARFRAC function gives you the total number of years between two dates (where full years are shown as integers and incomplete years are shown as decimals). The INT function then extracts the integer part from the result of YERAFRAC, which is the total number of years of service between the two given dates.
Calculating Years of Service Using the DATEDIF Formula
If you need to calculate the use of service where you may want 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 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 Excel. But when it comes to calculating the total years of service between two dates, this is the best function to use.
Calculating Only the Years of Service
Below I have a data set where I have the start date and end date for a set of employees, and I want to calculate the total number of years of service for each employee. Below is the formula that will do this: The DATEDIF formula takes the start and the end date as the first two arguments and gives the total years between the dates (as I have specified Y as the third argument).
Calculating Service Duration in Years and Months
Below I again have the same data set and I want to calculate the duration of service of each employee in years and months. Below is the formula that will do this: The above formula contains two DATEDIF functions
The first one is used to calculate the total number of completed years between the two datesThe second one is used to calculate the total number of months between the two dates, but does not include those months that have completed an year. For example, if the difference between two dates is 14 months, this would only return 2, as the first 12 months completed to become a year.
These DATEDIF functions are combined with the text “Years” and “Months”, so that the result is 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
And if you want to get the total service duration in Years, Months as Days, you can use the below formula: The above formula uses three DATEDIF functions to find out the numbers of years months and days in the total service and then combine these using the & operator. I have also added the words Years, Months, and Days to make the result easy to comprehend.
Calculating Service Duration Till Today
If you want to calculate the total time elapsed between a given date and the current date, you can use the same DATEDIF formula, and replace the end date with the TODAY formula. TODAY() formula gives you the current date based on your computer settings. It’s a volatile formula, which means that the next time you open the workbook or make a change in the workbook, this formula would be refreshed, and if the date has changed, then the new date would be used. Below I have a data set where I want to calculate the total years of service for each employee from their joining date till today. Here’s the formula that will do that: Note: You can also use this formula to calculate the age of a person when you have their date of birth
Calculate the Date After a Specific Duration of Service
Another scenario involving total tenure of service could be when you want the date after a specific number of years in service. For example, let’s say you’re the HR in a company that offers loyalty bonuses to employees who complete 10 years in service. You have a list of employees and their joining data shown below, and you need to know on what date their loyalty bonus would be due. Below is the formula that will give you this date: The EDATE formula is meant to return the date after or before the specified number of months, but we have hacked it to give us the date after 10 years. To do this, instead of specifying the number of months as the second argument, we have specified 10*12, which is the total number of months in 10 years. So these are some of the ways you can use to calculate the years of service in Excel and get the result in years, years, and months, or years months and days. I also covered how to get the date after a specific number of years using the EDATE formula. I hope you found this tutorial useful. Other Excel tutorials you may also like:
Get Day Name from Date in Excel (Easy Formulas)How to SUM values between two dates (using SUMIFS formula)Calculate the Number of Months Between Two Dates in ExcelHow to Calculate the Number of Days Between Two Dates in ExcelHow to Add Months to Date in Excel (Easy Formula)Check IF a Date is Between Two Given Dates in Excel (Easy Formula)
