Calculate the difference between two dates using DATEDIF Function

image_pdf

DATEDIF function is hidden function in Excel. The reason why I called it a hidden function because excel does not show it in its function list.

For example, just type =date in Excel. Microsoft excel will start suggesting functions related to Date. But it will not show DATEDIF function.

Hidden function DATEDIF

Note that it is "DATEDIF" function and not "DATEDIFF"

Download file and Try it yourself!!-

Download449 downloads

DATEDIF Function

The DATEDIF function is used when we want to get the number of days or years or months between two given dates.

Syntax:

=DATEDIF(Start_Date,End_Date,Unit)

where,

Start_Date-Starting date of the period you want to calculate.

End_Date-Last date of the period you want to calculate.

Unit-specifies the interval by which you want the difference to be returned. There are 6 Units, which are described as under:

UNIT RETURNS
“Y” Returns the period difference as complete years.
“M” Returns the period 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.

Want to customize Date and Time Format?

Example 1: Let's find difference of days between two dates-"04-03-2014" and "02-04-2017". Unit "d" returns the difference between two dates.

daydif

Example 2: Let's find difference of months between two dates-"04-05-2014" and "02-05-2017". Unit "m" returns the difference of month between two dates.

daydif1

Do you know how we can add two dates?

Example 3: Let's find difference of years between two dates-"03-02-2013" and "14-04-2015". Unit "y" returns the difference of years between two dates.

daydif2

Example 4: Let's find difference of days between two dates ignoring years and months-"01-02-2014" and "04-04-2016". Here it returns only 3 value because unit "md" does not consider change in month and years.

datedif -Only days ignoring months and years

Example 5: Let's find difference of months between two dates ignoring years and days-"01-02-2014" and "04-04-2016". Here it returns only 2 value because unit "ym" does not consider change in days and years.

 datedif -Only months ignoring days and years

Example 6: Let's find difference of days between two dates ignoring ONLY years.

daydif3

For any kind of queries on MS Excel, please feel free to Contact us.

Stay tuned with us on www.facebook.com/amazingxcel for more amazing tips on MS Excel.

Explore more, It's Amazing…

Want to know how many days you have to work in this month??

How to use Time functions in excel?


 

4,715 total views, 5 views today

3 thoughts on “Calculate the difference between two dates using DATEDIF Function

  1. Can u pls suggest how to Add No of Days with a Date restricted to 360 days in a year. System is using 365 days in a year by default. 

    Regds

    Srikandan S

     

  2. Hii SriKandan S,

    We can use DAYS360 function to calculate days between two dates based on 360 days

    Syntax: =DAYS360(Start_date, end_date, Method).

    Do check and provide your feedback

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>