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?.

More Excel Tips

• Formula Friday Using DATEDIF To Count The Number Of Months Between Two Dates
• Calculate The Number Of Days Between Two Dates In Excel- Extract Next Day Deliveries – Formula Friday
• Find The Difference Between Two Dates- DATEDIF
• Formula Friday – Find The Date Of The Fourth Business Day Of The Next Month.
• Formula Friday – Easily Calculate The Number Of Days Between Dates with the DAYS() Function