Assume a beginning date in cell A7 and ending date in cell B7. In range C7:E7, the task is to compute the years (in cell C7), remaining months (in cell D7) and remaining days (in cell E7). As an example, if one types in 12-March-2013 in cell A7 and 24-March-2014 in cell B7, then the result in range C7:E7 should be 1,12 and 12 respectively. The formulas for getting these results are:

1. **In cell C7** – =DATEDIF($A7,$B7,”y”)

2. **In cell D7** – =DATEDIF($A7,$B7,”ym”)

3. **In ****cell**** E7** – =DATEDIF($A7,$B7,”md”)

So far so good. All the results are correct. However, there is a problem with the “md” syntax of the DATEDIF() function. Try this

1. In cell A7, enter 31-July-2007

2. In cell B7, enter 02-March-2009

3. In cell C7, enter =DATEDIF($A7,$B7,”y”)

4. In cell D7, enter =DATEDIF($A7,$B7,”ym”)

5. In cell E7, enter =DATEDIF($A7,$B7,”md”)

The result in cell E7 will be -1 which is clearly incorrect. To get the correct result of 2 in cell E7, try this formula instead

=B7-EDATE(A7,(C7*12)+D7)

Hope this helps.

## The DATEDIF() bug

{ 17 Comments }