Hey All, it’s Formula Friday again. Today let’s look at a handy little formula called DATEDIF. DATEDIF is a function in Excel that calculates the difference between two dates. It can be used to find the number of years, months, days, or hours between two dates. This function can be helpful for determining how long ago an event occurred or for tracking deadlines. This blog post will show you how to use the DATEDIF function and provide some practical examples.
Where Is DATEDIF?
Strangely Excel does not provide any help or documentation on this since Excel 2000, but it is still available. Only if you type the full formula in yourself. Let’s go ahead and kind =DATE into Excel. Only these two options appear so it is one of the few Excel formulas you will need to learn if you want to use it.
DATEDIF Syntax
The syntax of the DATEDIF formula is =DATEDIF(Start_Date,End_Date,Interval).
- The Start_Date is the starting period that you want Excel to calculate. (This can be entered as a number, formula or string).
- The End_Date is the end or last date of the period that you want Excel to calculate. (This can also be entered as a number, formula or string).
- The Interval is how you want Excel to display the difference between the two dates and there are 6 different types of units available.
Here is an example.
Question- Find how many days have passed since 01 January 2000.
In this case it is 5333 days from 01 January 2014 until 08 August 2014. Let’s change the days (“D”) and find he number of months (“M”) between the two dates.
The number of months is 175.
Some points to note about the DATEDIF function.
If Excel returns a #NUM error it could be because
- The End Date is greater than the Start Date.
- One Interval is invalid.
If Excel returns a #Value error then one of your dates in not valid.
DATEDIF always rounds months down to the last complete number of months.
There are a few more units or intervals that can be calculated in this formula, a summary can be seen below. Have you used DATEDIF in Excel?.
| UNIT | Explanation |
|---|---|
| “Y” | Returns the difference as complete years. |
| “M” | Returns the difference as complete months. |
| “D” | Returns the number of days in the period. |
| “MD” | Returns the difference between the days in ‘Start_Date’ and ‘End_Date’. Here the months and years of the dates are ignored. |
| “YM” | Returns the difference between the months in ‘Start_Date’ and ‘End_Date’. Here the days and years of the dates are ignored |
| “YD” | Returns the difference between the days of ‘Start_Date’ and ‘End_Date’. Here the years of the dates are ignored. |
More Excel Tips
