Datedif

My job requires a lot of calculation involving elapsed time in whole months. I use DATEDIF all the time to do this. I recently ran into one that doesn't make sense to me.

Start Date: 01/01/1987
End Date: 09/30/1987

=DATEDIF(01/01/1987,09/30/1987,"M") returns a result of 8 months. I should be getting a result of 9 though, the completion of the month of September should grant a month. I tweaked the formula a bit to use a start date of 12/31/1986 but it still returned a result of 8 months. The only way I was able to get a result of 9 months was to use a start date of 12/30/1986 or an end date of 10/01/1987. I can't really do either of those things. Why isn't this working as expected.

I didn't have any better luck trying to use YEARFRAC either. Does anyone have a better idea for elapse time functions?

Thanks a bunch.

You don't have to change the start or end dates 'On the sheet'.
You can do it within the formula itself..

Try
=DATEDIF(A1,A2+1,"m")

Originally Posted by Jonmo1

You don't have to change the start or end dates 'On the sheet'.
You can do it within the formula itself..

Try
=DATEDIF(A1,A2+1,"m")

So from a conceptual perspective; if I want a DATEDIF function to consider the end date as a "completed" date I should add +1 to the end date so it actually ends it on the following day?

Yeah, I think the logic of the function would be it counts how many months have 'Passed' between the 2 dates.
If the End date is the last date of the month, then that month hasn't actually 'Passed' yet.

I'm really just pulling that out of my keester, not sure if it's true or not.

I would have thought the same, and that's why I was so confused when =DATEDIF(12/31/1986,09/30/1987,"M") also returned 8 months.

Well, what IS an accurate result ?

Given that the number of days in a month is not consistent, it's really impossible to say with any precision how many months have passed between 2 dates.
They did the best they could, and this is what they gave us.

Perhaps an alternate method would be to use 30.4375 as a constant representing the number of days in a month
=(365.25*4)/48

Now do
=(A2-A1+1)/30.4375

That's going to give you a decimal number of the number of months, probably need to round it down
=INT((A2-A1+1)/30.4375)

Thanks. I'll give it a shot.