Calculate # of months if a date is given

[LP]

I have 2 columns with date range. A1 is start date of 3/1/2010 and
B1 end date of 3/1/2011. Is there a way to show the number of months
ONLY in 2010? I realized the question has been asked before but I
believe it's to calculate the number of months between the 2 dates.
This is specific to the year I want. Thanks.

 

[JoeU2004]

A1 is start date of 3/1/2010 and B1 end date of
3/1/2011. Is there a way to show the number of months
ONLY in 2010?

Depends on your definition of "number of months between".

Perhaps:

=if(year(B1) > year(A1), 13 - month(A1), month(B1) - month(A1) - (day(B1) <
day(A1)))

So there are 10 months in 2010 between 3/2/2010 and 3/1/2011, but there are
8 months in 2010, not 9, between 3/2/2010 and 12/1/2010.

If you would like the latter to be 9, then:

=if(year(B1) > year(A1), 13, month(B1)) - month(A1)

Note: These formulas assume B1 >= A1; i.e. the end date is on or after the
start date. Is that an acceptable assumption.


----- original message -----

 

[Fred Smith]

Try:

=datedif(a1,min(b1,date(year(a1),12,31)),"m")

Regards,
Fred.

 

[JoeU2004]

Retraction....

=if(year(B1) > year(A1), 13 - month(A1),
month(B1) - month(A1) - (day(B1) < day(A1)))

That formula is correct for certain combinations, for example: (a) start
12/31/2010, end 1/1/2011; and (b) start 10/31/2010, end 11/30/2010.


----- original message -----

 

[JoeU2004]

Try:
=datedif(a1,min(b1,date(year(a1),12,31)),"m")

Result is 0 for start 12/1/2010, end 1/1/2011. Shouldn't that be 1?

Result is 0 for start 10/31/2010, end 11/30/2010. Not clear what answer
"LP" would like in that case.

Result is 8 for start 3/2/2010, end 12/1/2010. As I noted, some people
might want 9. Not clear what "LP" wants.


----- original message -----