< Go Back

Get days, months, and years between dates

The DATEDIF function in Excel is very useful for calculating the difference between two dates in terms of years, months or days. In this post, we will take a look at how to get days, months and years between dates in Excel.

Formula

=DATEDIF(start,end,"y")&"years,"&DATEDIF(start,end,"ym")&"months,"&DATEDIF(start,end,"md")&"days"

Explanation

The above formula is based on the DATEDIF function in excel. The DATEDIF function requires three arguments:

  • start_date: Starting date (can be a string value or cell reference)
  • end_date: Ending date (can be a string value or cell reference)
  • unit: The unit you want the difference to be calculated in

The unit argument has 6 possible input values.

  • “Y”: Difference in total years
  • “M”: Difference in total months
  • “D”: Difference in total days
  • “YM”: Difference in months, with years ignored (“Y” Modulo 12)
  • “YD”: Difference in days, with years ignored (“D” Modulo 365)
  • “MD”: Difference in days, with years and months, ignored

The formula above can be divided up into different parts to make it more readable:

=DATEDIF(start,end,"y")

&” years, “

&DATEDIF(start,end,"ym")

&” months, “

&DATEDIF(start,end,"md")

&” days”

The & operator can be used in Excel for concatenating (adding) two strings together to make a more meaningful string. We will see how this works in the example below.

Example

Consider the following scenario where we have sample data for former employees in a company, along with their joining date and their resignation date.

Figure 1. Sample former employee data with start and end date

We want to calculate the duration of each employee’s tenure. We will use the following formula:

=DATEDIF(C5,D5,"y")&"years,"&DATEDIF(C5,D5,"ym")&"months,"&DATEDIF(C5,D5,"md")&"days"

Figure 2. Get days, months, and years between dates

Although this formula has worked perfectly, there is a slight problem with the display. You can see that the results also include zero values, such as “0 years”, “0 months” and so on. In the next step, we will get rid of these zero values.

Ignoring zero values

The IF function in Excel can be used to apply a check on a value, and output a result based on whether that check fails or passes. The basic syntax for the IF function is:

=IF(condition,“PassOutput”,”FailOutput”)

In our scenario, we will use the IF function to display a value for days, months and years only if they’re non-zero. In case of zero, nothing will be displayed.

=IF(DATEDIF(C5,D5,"y"),DATEDIF(C5,D5,"y")&"years","")

&IF(DATEDIF(C5,D5,"ym"),DATEDIF(C5,D5,"ym")&"months","")

&IF(DATEDIF(C5,D5,"md"),DATEDIF(C5,D5,"md")&"days","")

Note: We haven’t explicitly checked if values are zero, because if the first expression in the IF function evaluates to zero, it is automatically treated as a false condition, whereas if there is a non-zero value present, it is treated as a true condition.

Here is the final result, which looks much neater:

Figure 3. Final result after removing zero values

Notes

  • The start date should always be less than the end date, otherwise, the DATEDIF function will throw a #NUM error.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar