The DATEDIF function in Microsoft Excel calculates the difference, or interval, between two dates. This difference can be expressed in a variety of ways. The function takes the form
=DATEDIF(Date1, Date2, Interval)
where Date1 and Date2 are the two dates and Interval defines how the date difference should be returned. Interval must be enclosed in quotes like this:
=DATEDIF(Date1, Date2, “d”)
These are the errors that DATEDIF may return if you get something wrong:
- #NUM error if Date1 is later than Date2
- #VALUE error if either Date1 or Date2 are invalid dates
- #NUM error if the Interval specified is not one of the allowed values
The Interval must be one of the following values:
| Interval |
Use |
| m |
Months: the number of whole calendar months between the two dates |
| d |
Days: the number of days between the dates |
| y |
Years: the number of whole calendar years between the dates |
| ym |
Months In Same Year: the number of months between the two dates if they were in the same year |
| yd |
Days In Same Year: the number of days between the two dates if they were in the same year |
| md |
Days In Same Month And Year: the number of days between the two dates if they were in the same month and year |
|
|
DATEDIF Examples
Try and predict what the result of the following examples will be:
| DATEDIF |
Result |
| =DATEDIF(01/01/2010, 01/06/2010, “d”) |
151 |
| =DATEDIF(01/01/2010, 15/01/2010, “d”) |
14 |
| =DATEDIF(01/01/2010, 01/06/2010, “m”) |
5 |
| =DATEDIF(01/01/2010, 15/01/2010, “m”) |
0 |
| =DATEDIF(01/01/2009, 01/06/2010, “m”) |
17 |
| =DATEDIF(18/08/2008, 01/03/2010, “y”) |
2 |
| =DATEDIF(01/01/2010, 31/12/2010, “y”) |
0 |
| =DATEDIF(01/01/2008, 01/06/2010, “ym”) |
5 |
| =DATEDIF(23/04/2003, 31/05/2005, “yd”) |
38 |
| =DATEDIF(17/02/1974, 28/01/1998, “md”) |
11 |
