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.
Note that it is "DATEDIF" function and not "DATEDIFF"
Download file and Try it yourself!!-
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.
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.
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.
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.
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.
Example 6: Let's find difference of days between two dates ignoring ONLY years.
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?
5,336 total views, 2 views today
Nice,,,thankssssssss
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
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