DateDiff Mystery XL2007

[mmac7314]

Hi,

I am quality checking some work that took a few weeks to complete. I used the DateDif function to calculate the number of whole months between two dates. Here is an example:

=DATEDIF("9/18/2014","2/17/2015","m")
Result = 4

I know that this function will round down to the nearest whole number. Why isn't the result = 5?

Numbers below re whole or fractions of a month

9/18/2014 0.4 (12/30)
Oct-2014 1.0
Nov-2014 1.0
Dec-2014 1.0
Jan-2015 1.0
2/17/2014 0.61 (17/28)
Total 5.01 months

 

[Joe4]

Welcome to the Board!

I know that this function will round down to the nearest whole number. Why isn't the result = 5?

It looks at the days to determine if it is a whole month.
If the day of the start date is "18", it won't be a whole month until the day of the ending date is greater than or equal to "18".

So,

[COLOR=#333333]=DATEDIF("9/18/2014","2/[/COLOR][COLOR=#ff0000][B]18[/B][/COLOR][COLOR=#333333]/2015","m") [/COLOR]

will return 5
but

[COLOR=#333333]=DATEDIF("9/18/2014","2/[/COLOR][COLOR=#FF0000][B]17[/B][/COLOR][COLOR=#333333]/2015","m") [/COLOR]

will return 4

 

[mmac7314]

Thanks for the welcome! So to put it another way there Oct > Jan = 4 whole months. then there are 12 days in September and 17 days in Feb = 29. So the calculation is 4 months, 29 days, which rounds down to 4. ??

 

[Joe4]

It is not doing any rounding, and it doesn't count partial months. It only returns complete months.

So, if your start date is "9/18/2014", it becomes:
1 complete month on "10/18/2014",
2 complete months on "11/18/2014",
3 complete months on "12/18/2014",
4 complete months on "1/18/2015",
5 complete months on "2/18/2015",
etc.

See here for more details: https://exceljet.net/excel-functions/excel-datedif-function

 

[mmac7314]

Ah!!!!! got it. Thank you!

 

[Joe4]

You are welcome.