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

On Sun, 25 Jan 2009 17:09:31 +0900, Ricky
wrote:

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:10 00)+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

That's great! Thanks Lars-Åke

Lars-Åke Aspelin wrote:
On Sun, 25 Jan 2009 17:09:31 +0900, Ricky
wrote:

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:10 00)+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

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

"Ricky" wrote in message
...
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

On Sun, 25 Jan 2009 09:36:23 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

=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

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

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

"Ricky" wrote in message
...
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

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

--
Gary''s Student - gsnu200828


"David Biddulph" wrote:

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

"Ricky" wrote in message
...
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

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

Thanks guys for all your help.

Gary''s Student wrote:
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