Excel DATEDIF Function: Calculates the difference between two dates

DATEDIF is a hidden function in Excel. As the name suggests the job of this function is to calculate the difference between two given dates.

For some reason Microsoft has decided not to document this function. And because of this you won’t find this function in the Formula Tab.

To verify this, try and type =DATE in any cell. You will see, Excel enlists all the functions that start with the word “date” but it doesn’t shows DATEDIF.

The Syntax of DATEDIF function is as follows:

=DATEDIF (Start_Date, End_Date, Interval Type)

Here, ‘Start_Date’ is the starting date of the period that you wish to calculate. ‘Start_Date’ can be entered as a string within double quotes (like: “6/6/2017”), it can also be entered as a serial number, as Excel internally treats dates as serial numbers (for example: the number 42892 represents “6/6/2017”, if you are using the 1900 date system), or you can also supply the dates as a formula (like: =DATE (2017, 6, 6)).

‘End_Date’ is the last date of the period that you wish to calculate. Similar to ‘Start_Date’, ‘End_Date’ can also be entered as a string, number or a formula.

‘Interval Type’ specifies the interval by which you want the difference to be returned. There are 6 ‘Units’, which are described as under:

Some Important points about DATEDIF Formula:

  • DATEDIF throws a #NUM error is Start_Date is greater than ‘End_Date’.
  • It throws a #Value error if anyone of the date arguments is invalid.
  • It also throws a #NUM error if the ‘Interval Type’ is invalid.

Be the first to comment

Leave a Reply

Your email address will not be published.