# Excel Datedif function - inconsistent in some dates

#### mb8marmed

##### New Member
I found DATEDIF function to be inconsistent in calculating dates. Sometimes it will calculate the exact number of days but sometimes it will be short by one day. Also, February is only counted to be 28 days and not one month (see item no. 11). In addition, what will be the rule of thumb for calculating item no. 9?

 Item​ Start Date​ End Date​ Month-Day (by DATEDIF Function)​ Month-Day (by Manual counting)​ Remarks​ 1​ 10/30/2021​ 12/04/2021​ 1 month & 5 day​ 1 month & 6 day​ short by 1 day 2​ 11/02/2021​ 11/30/2021​ 0 month & 29 day​ 0 month & 29 day​ ok 3​ 10/28/2021​ 11/30/2021​ 1 month & 3 day​ 1 month & 4 day​ short by 1 day 4​ 11/01/2021​ 12/04/2021​ 1 month & 4 day​ 1 month & 4 day​ ok 5​ 10/31/2021​ 12/01/2021​ 1 month & 1 day​ 1 month & 2 day​ short by 1 day 6​ 01/01/2021​ 03/03/2021​ 2 month & 3 day​ 2 month & 3 day​ ok 7​ 08/15/2020​ 11/01/2020​ 2 month & 18 day​ 2 month & 18 day​ ok 8​ 08/30/2020​ 12/12/2020​ 3 month & 13 day​ 3 month & 14 day​ short by 1 day 9​ 08/20/2020​ 11/27/2020​ 3 month & 8 day​ 2 month & 39 day​ How to deal with 39 days, and what rule should apply? 10​ 05/25/2020​ 09/06/2020​ 3 month & 13 day​ 3 month & 13 day​ ok 11​ 02/01/2021​ 02/28/2021​ 0 month & 28 day​ 1 month & 0 day​ Should be 1 month

Formula used in column D is =DATEDIF(B3,C3,"m") & " month "& "& " & DATEDIF(B3,C3,"md")+1 & " day"

Bert

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### jasonb75

##### Well-known Member
I found DATEDIF function to be inconsistent in calculating dates.
If you read the excel help file it advises against the use of "md" and gives you an alternative.

I have no idea what you're trying to achieve with 2 months and 39 days, that defies all logic.

Replies
8
Views
175
Replies
5
Views
345
Replies
7
Views
164
Replies
0
Views
209
Replies
15
Views
162

1,148,249
Messages
5,745,627
Members
423,964
Latest member
Dustin M

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back