DATEDIF returns incorrect count

[kasmith1017]

Hi everyone,

I am putting in a column to proof out that I have the correct balance for my prepaid item based on the number of months remaining based on the end date of the item and the current month end. I have my current month end date (7/31/17) in cell Z4, and the contract end date in cell F11. In a new cell I've put the formula below:

=DATEDIF(Z4,F11,"m")

When I do this, the result that returns is a 1, not a 2 as expected. All others worked out OK but this one. Can someone please help me out? I'm thinking maybe it has to do with the number of days in each month. Basically I want to get the number of months between these two dates so if there's a better formula I can use that will not cause this issue please let me know. Thank you in advance!

 

[kasmith1017]

Sorry, I forgot to add that this is only on the items where the end date is 9/30/17.

 

[Dave Patton]

Try the following

Excel 2010
	B	C	D	E	F	G	H
1							Datedif Months
2	31-Jul-17	31-Aug-17	30-Sep-17	31-Oct-17	30-Nov-17	31-Dec-17	31-Jan-18
3		1	2	3	4	5	6
4
6c

Cell Formulas
Range	Formula
C3	=DATEDIF($B$2+1,C2+1,"m")

 

[Rick Rothstein]

If you are always going from the end of one month to the end of the other month, why not just subtract the two month values...

=MONTH(F11)-MONTH(Z4)

 

[kasmith1017]

If you are always going from the end of one month to the end of the other month, why not just subtract the two month values...

=MONTH(F11)-MONTH(Z4)

Great question! Just figured if there was a way I could get the number of months without dividing by 30 days I would do that, but clearly that's not working out. Thank you all for the help!