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.
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:
&” years, “
&” months, “
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.
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:
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:
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.
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
- The start date should always be less than the end date, otherwise, the DATEDIF function will throw a #NUM error.