Having a formula for calculating years of service in Excel will make it easier for accountants to track and calculate the number of years employees have worked in the company, saving time and reducing calculation errors.
If you're familiarizing yourself with common calculation functions in Excel , then the seniority calculation formula is one you should prioritize.
When it comes to date and time calculation functions, we often think of the Date function. However, this article will discuss a function that combines the Date function, called DateDIF, which uses the DateDIF function to count days, months, and years in Excel. Learn how to use the DateDIF function below.
Use the DateDIF function to calculate years of service.
Date IF is a function that allows users to count the number of days, months, and years from a given point in time to another given point in time or to the current point in time, and returns the result in date count format. The Date IF function is widely used to calculate date intervals due to its high accuracy, various return types, and extreme ease of use.
DateDIF function formula
= DATEDIF (Start date, End date, optional return result)
- In this formula, DateDIF is the name of the function for counting days and months.
- Start date: The start date is predetermined, which could be the day you started school, work, or got married.
- End date: The date a job or event ended, or simply the current date.
- Return result options: There are 6 types of results to return when you calculate years of service in Excel, and you can choose to count years, months, or days as you wish.
* "d": Counts the number of days.
* "m": Counts the number of months, but only takes the integer part.
* "y": Counts the number of years in between, also only taking the integer part .
* "yd": Returns the number of odd days in a year when subtracting two time intervals .
* "ym": Returns the number of odd months in a year when subtracting two time intervals.
* "md": Returns the number of odd days in a month.
How to calculate years of service in Excel using the DateIF function
Let's assume we have a list of 10 employees in a company with contracts at different times. Note that these contracts are based on their initial employment with the company, and the requirement is to return the number of working days. If the number of days is greater than 30, the result should be in months, and if the number of months is greater than 12, the result should be in years.
Step 1: Before calculating years of service in Excel, you must check if the date column format is correct or if you have set it to the correct Vietnamese date format.
- Select the cells you want to calculate and right-click, then choose > Format Cells .
Step 2: In the first "Number" section, select "Catalogue " as "Date," choose " Type " as you like, but make sure " Locate " is Vietnamese. Then confirm with "OK" to allow the system to adjust the date and time to Vietnamese in Excel for you.
Step 3: In the seniority table, we begin entering the formula in the first empty cell, D6 , as follows:
=DATEDIF(C6,TODAY(),"y")&" year "&DATEDIF(C6,TODAY(),"ym")&" month "&DATEDIF(C6,TODAY(),"md")&" day"
The formulas can be explained as follows:
- =DATEDIF: Function used to count days, months, and years.
- C6: The first position in the contract table corresponding to the start date.
- TODAY: The result returns today's date.
- "y": Counts the number of years in between, also taking only the integer part.
Additionally, we see the use of the '&' character to connect multiple functions together. Here, TipsMake has used three identical functions, but with different return values: year, month, and day.
Step 4: After pressing Enter, you will get the result of 10 years, 5 months, and 21 days, corresponding to the contract signing date and the calculation date. The next step is quite familiar; you just need to scroll down to apply the formula to all the objects below.
And the final result after calculating years of service in Excel is that you will get a list that satisfies the above conditions; we get an accurate list.
So, we've just learned more about calculating years of service in Excel using the DatedIF function, a very useful function in date and time calculations. Practice using this function more so you can understand and combine it with many other functions in Excel.
If you're already familiar with DateDIF, you should learn more about the Date and Datevalue functions in Excel to create dates based on separate year, month, and day values, making it easier to manage and perform calculations with date values.