How to Calculate Tenure in Years and Months in Excel

Get FREE Advanced Excel Exercises with Solutions!

Tenure refers to the service period of an employee. Employers often need to keep track of the tenure of their employees for various reasons. Excel can help in this matter. In this article, we will learn 5 different approaches how to calculate tenure in years and months in Excel.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


5 Easy Methods to Calculate Tenure in Years and Months in Excel

In this article, we will learn 5 easy methods to calculate tenure in years and months in Excel. We will use the following dataset to demonstrate these methods. In the following dataset, we have shown the Joining Date and Leaving Date of some employees. Now we will calculate their tenure.

how to calculate tenure in years and months in Excel


1. Apply DATEDIF Function to Calculate Tenure in Years and Months

This is the most convenient method to calculate tenure in years and months in Excel. We will use the DATEDIF function for this purpose. Follow the steps to calculate tenure using the DATEDIF function.

Steps:

  • First, select the empty cell E5.
  • Then write down the following formula.
=DATEDIF(C5,D5,"y") & " Years " &DATEDIF(C5,D5,"ym") & " Months "
  • Here, C5 refers to the joining date and D5 refers to the leaving date.
Formula Breakdown
  • DATEDIF(C5,D5,”y”) returns the number of years in integer form after subtracting the leaving date of D5 from joining date C5.
  • DATEDIF(C5,D5,”ym”) returns the number of months for the fraction value of years.

how to calculate tenure in years and months in Excel using DATEDIF function

  • Next, Press Enter. You will see the desired result in cell E5.
  • Then drag the bottom right corner of cell E5 to E12 by holding the left button of the mouse.

how to calculate tenure in years and months in Excel using DATEDIF function

  • Thus you will get the values of years and months for all the dates.

Read More: How to Calculate Average Tenure of Employees in Excel


2. Insert LET Function

Now you will see another approach to calculate tenure in years and months format. We will use the LET function in the following procedure.

Steps:

  • First of all, click on cell E5.
  • Now type the following formula in the cell.
=LET(start,C5,end,D5,DATEDIF(start,end,"y")&" Years "&DATEDIF(start,end,"ym")&" Months")
  • In the formula, C5 and D5 refer to the joining and leaving dates respectively.
Formula Breakdown

LET(start,C5,end,D5, assigns the name start for cell C5 and end for cell D5.

DATEDIF(start,end,”y”)&” Years “&DATEDIF(start,end,”ym”)&” Months”) looks into the start date and end date and returns tenure value in years and months.

how to calculate tenure in years and months in Excel using LET function

  • Hit Enter to see the result in cell E5.

how to calculate tenure in years and months in Excel using LET function

  • Now, if you want to see the results for all the data, just double-click the bottom right corner of cell E5.
  • It will show the results in years and months format.

Read More: How to Apply Excel Formula to Count Days from Date to Today


3. Combine INT and ROUND Functions to Calculate Tenure

In this procedure, we will demonstrate the calculation of tenure by combining two functions (INT & ROUND). Keep on reading to learn the steps.

Steps:

  • To begin with, move your cursor to the cell E5 and click on it.
  • Then write down the formula given below:
=INT((D5-C5)/365.25)&" Years "& ROUND((((D5-C5)/365.25)- INT((D5-C5)/365.25))*12,0)&" Months"
Formula Breakdown

INT((D5-C5)/365.25 subtracts the date from cell D5 to C5. Then divides the value by 365.25 and returns years in integer format. In cell E5, it returns 17 Years.

ROUND((((D5-C5)/365.25)- INT((D5-C5)/365.25))*12,0) multiplies the fraction part of the year and round it off to return the value of months. In cell E5, it returns 5 Months.

how to calculate tenure in years and months in Excel using INT & ROUND function

  • To get the result, press Enter.

how to calculate tenure in years and months in Excel using INT & ROUND function

  • Then double-click the bottom right corner of E5 cell and see the results for all the dates.

Read More: Excel Formula to Calculate Number of Days Between Today and Another Date


Similar Readings


4. Use DAYS Function

Now you will learn how to calculate tenure in years and months in Excel using the DAYS function.

Steps:

  • First, select the cell (E5) where you want to show the result.
  • Next, type the following formula in the cell.
=INT(DAYS(D5,C5)/365.25)&" Years "& ROUND(((DAYS(D5,C5)/365.25)- INT(DAYS(D5,C5)/365.25))*12,0)&" Months"
Formula Breakdown

INT(DAYS(D5,C5)/365.25) divides the number of days by 365.25 and returns the results in integer format.

ROUND(((DAYS(D5,C5)/365.25)-INT(DAYS(D5,C5)/365.25))*12,0) multiplies the fraction part of the year and round it off to return the value of months.

how to calculate tenure in years and months in Excel using DAYS function

  • To get the tenure years and months, hit the Enter button on your keyboard.

how to calculate tenure in years and months in Excel using DAYS function

  • Then you can get the results for all the data by moving the cursor at the bottom right corner and clicking the left-button of the mouse twice.

Read More: Excel Formula to Find Date or Days for Next Month (6 Quick Ways)


5. Utilize YEARFRAC Function

In this method, we will use the YEARFRAC function for tenure calculation. Let us describe the process below:

Steps:

  • First of all, go to cell E5 and select it.
  • Then write down the formula below in that cell.
=INT(YEARFRAC(C5,D5))&" Years "& ROUND((YEARFRAC(C5,D5)- INT(YEARFRAC(C5,D5)))*12,0)&" Months"
Formula Breakdown

INT(YEARFRAC(C5,D5)) returns the number of years between the date of C5 and D5 in integer format.

ROUND((YEARFRAC(C5,D5)-INT(YEARFRAC(C5,D5)))*12,0) multiplies the fraction part of the year and round it off to return the value of months.

how to calculate tenure in years and months in Excel using YEARFRAC function

  • Now press Enter Years and months of tenure will be shown in the cell E5.

how to calculate tenure in years and months in Excel using YEARFRAC function

  • To calculate tenure for all the employees, click the bottom right corner of cell E5 two times.

how to calculate tenure in years and months in Excel using YEARFRAC function

Read More: How to Calculate Years Between Two Dates in Excel (2 Methods)


Things to Remember

  • While using a formula, don’t forget to give proper cell references or you won’t get the desired results.
  • Be careful about using the parenthesis while writing any formula. Parenthesis in the wrong position might show wrong results.

Concluding Remarks

Thanks for making it this far. I hope you find this article useful. Now you know 5 different methods to calculate tenure in years and months in Excel. Please let us know if you have any further queries and feel free to give us any recommendations in the comment section below.


Related Articles

Alif Bin Hussain

Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo