Calculating Difference Between Months

I am trying to calculate the difference between 2 months and thought about using the DATEDIF() formula but it only accounts for whole months. For example, if my 2 dates are January 1, 2008 and February 20, 2008, the DATEDIF() formula only grabs the 1 month in between (the full month of January in this case). Is there another formula that will account for those 20 days in February and round up in this case to 2 months? Even better, to 1.5 months if that is the closest match?



TIA.

Try this

Please Login or Register  to view this content.

I found it here.

http://www.pcreview.co.uk/forums/thread-1055640.php

Interesting. That works in my example that I previously posted. But try these dates: 6/30/08 and 6/29/09. That returns 11 and probably should be 12 months.



TIA.

Counting half a month as 15.2 days, this rounds to the nearest ½ month

=ROUND((B1-A1)/15.2,0)/2

Definitely disappointed in Excel in this case. You would think it would have the ability to calculate this pretty easily-- it can do almost everything else. Very odd.

Thanks!

When people want to count a number of months the difficulty, I believe, lies in defining exactly what is meant by "a month", that isn't an excel issue, as such, it's the difficulty caused by using a unit with variable length.

It's much easier to count using units which don't vary, e.g. days or weeks.

Does the formula I suggested work for you? If not can you post some representative examples of data with required results.

The 15.2 works but it isn't as scientific as if you were actually using a calendar. I had a formula in originally based on 30.4 and was just trying to find a way to use a formula that Excel would look at a calendar to determine the true number of months-- no slippage. Make sense?


TIA.

But what is the true number of months [and days]?

If you want to count from 13th January 2007 to 8th March 2007 how many months and days is that? You're most likely to say, I think, that 13th Jan to 13th Feb is 1 month and then 13th Feb to 8th March is 23 days, so the answer is "1 month 23 days" [of course it's yet another argument to determine that as a decimal, do you divide 23 by 28, 31 or something in between?]

But you could also make a case for "1 month 26 days" by counting backwards:

8th March back to 8th Feb is 1 month then 8th Feb back to 13th Jan is 26 days