Completed Months between Dates

[Ricky]

Hi - I'm after a formula to find the number of completed months between
2 dates. This may at times go through to the next year - for example:

Cell A1 I have "11:00AM 15/06/09"
Cell A2 I have " 3:00PM 25/02/10"
Cell B3 I want " 7 " to be returned.

Cells A1 and A2 are formatted as hh:hh AM/PM dd/mm/yy

Can anyone suggest a formula I can place in cell B3 to return the number
of completed months?

Tks, Ricky

 

[Lars-Åke Aspelin]

Hi - I'm after a formula to find the number of completed months between
2 dates. This may at times go through to the next year - for example:

Cell A1 I have "11:00AM 15/06/09"
Cell A2 I have " 3:00PM 25/02/10"
Cell B3 I want " 7 " to be returned.

Cells A1 and A2 are formatted as hh:hh AM/PM dd/mm/yy

Can anyone suggest a formula I can place in cell B3 to return the number
of completed months?

Tks, Ricky

Try this formula in cell B3:

=MAX(ROW(1:1000)*(DATE(YEAR(A1),MONTH(A1)+ROW(1:1000)+1,1)<=A2))

Note: this is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

The 1000 in two places can be replaced with any number that is bigger
than the biggest expected result.

Hope this helps / Lars-Åke

 

[Ricky]

That's great! Thanks Lars-Åke

Try this formula in cell B3:

=MAX(ROW(1:1000)*(DATE(YEAR(A1),MONTH(A1)+ROW(1:1000)+1,1)<=A2))

Note: this is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

The 1000 in two places can be replaced with any number that is bigger
than the biggest expected result.

Hope this helps / Lars-Åke

 

[David Biddulph]

=DATEDIF(A1,A2,"m") would give the number of completed months, which would
be 8, not 7.

 

[Lars-Åke Aspelin]

=DATEDIF(A1,A2,"m") would give the number of completed months, which would
be 8, not 7.

I guess the OP wanted the number of complete, not completeD, months,
i.e. number of complete months excluding the months of start and end
rather than the time difference measured in a month scale.

Lars-Åke

 

[Ricky]

Thanks David - I should have been a little clearer in my original post.
I'm actually after the number of complete months between the two dates,
so 7 does suit my needs. I do see where you're coming from though.

Regards, Ricky

 

[Gary''s Student]

You are correct David. Seven is correct only if the OP wants the count of
FULL completed months:

July 2009
August 2009
September 2009
October 2009
November 2009
December 2009
January 2010

 

[Ricky]

Yep, that's what I was afetr - teh FULL completed months only.

Thanks guys for all your help.