In the past month, I received numerous questions on how to display the difference between dates in years, months and days. Recently an HR consultant asked how she could show the time served by employees in years, months and days. She currently only displays the time served in years and wanted to be more specific. An inventory controller also asked me a similar question in relation to stock movement.
Therefore in this week’s tip we will practice using the DATEDIF and concatenate functions. The DATEDIF function returns the difference between two dates and the concatenate function joins the various formulas into one function.
Note: You are welcome to download the workbook to practice this exercise
Applies To: Microsoft Excel 2010 and 2013
- Select the Practice worksheet.
- Place the cursor in cell C4.
- Enter the formula below.
=DATEDIF(A4,B4,”Y”) &” Years, ” & DATEDIF(A4,B4,”YM”) & ” Months, “& DATEDIF(A4,B4,”MD”) &” Days,”
- The first DATEDIF formula returns the date difference in years.
- A4 represents the start date and B4 the end date.
- The second DATEDIF formula returns the date difference in months.
- ‘YM’ displays the remainder of the months after extracting the years.
- ‘M’ will display the total number of months.
- The third formula returns the date difference in days.
- ‘MD’ displays the remainder of the days after extracting the months.
- ‘D’ will display the total number of days.
- The ampersands (&) joins the three formulas into one.
The DATEDIF formula will have to be typed in because it is not available on the functions list. It is used for returning the difference between the start and end dates. However as in this example it can be nested to create a custom formula .
