Excel Datedif function - inconsistent in some dates

mb8marmed

New Member
Joined
Feb 15, 2020
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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"

Thank you in advance.

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
Joined
Dec 30, 2008
Messages
14,564
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top