In this tutorial, you will learn to write a formula to calculate the year of service in Excel. And for this, you are going to use the DATEDIF function. If you never heard about this function, you learn about it here.
Get the Number of Years of Service
In the following example, we have a start date and an end date.
And now let’s use the following steps:
- Type “=DATEDIF(” in cell C2.
- Refer to the start date, which is A2, and enter a comma.
- Refer to the end date, which is B2, and enter a comma.
- Enter “y”, enter close parentheses, and hit enter to get the result.
Let me explain: When you use “y” in the DATEDIF function it returns the complete year that you have between the start date and the end date.
=DATEDIF(A2,B2,"y")Now let’s say you want to get a complete period of service between two dates, which includes the year, month, and dates. You can use the following formula.
=DATEDIF(A2,B2,"y")&" year(s), "&DATEDIF(A2,B2,"ym")&" months(s), "&DATEDIF(A2,B2,"md")&" days(s)."
Get Years of Service from Today’s Date
Now let’s say you want to calculate years of service by using today’s date. Following is the formula that you need to use.
=DATEDIF(A2,TODAY(),"y")&" year(s)"