ROUNDING UP monthly differences

[Guest]

Hi every1

I want to calculate the number of months between two dates.

Datedif() may be used but upon a difference of less than 15 days it rounds
off the balance to 0 months.

I want to Round Up the difference upon a fractional month i.e. even 1 day
difference returns a 1 month difference and 13 months and 1 day to be showing
14 months!

All assistance shall highly be obliged!

 

[Bob Phillips]

=DATEDIF(A1-DAY(A1)+1,DATE(YEAR(A2),MONTH(A2)+1,1),"M")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

=MAX(DATEDIF(A1,EOMONTH(B1,0),"m"),1)

 

[Guest]

I want to calculate the number of months between two dates.
[....]
I want to Round Up the difference upon a fractional month i.e.
even 1 day difference returns a 1 month difference

=month(a2)-month(a1)+12*(year(a2)-year(a1))

 

[Guest]

Sorry Bob

But upon a starting date of Aug 31, 07 and ending date Sep. 1 2007, i.e. a
difference of only 1 day your offered formula returns the value "2" months.

 

[Guest]

Sorry Mike

Upon a starting date of Aug 01, 07 I was expecting your formula to be
returning a result of 1 upon the ending date being set at even Aug 02, 07 and
similarly a result of 2 upon ending date of Sep. 2, 2007, i.e. a difference
of 1 month and a 1 day being rounded up.

However, upon even dates like (beg.) Aug 01, 2007 and (end.) Sept. 30 a
difference of only 1 month is being returned by your formula instead of 2.

 

[Guest]

Sorry Joe

Upon a starting date of Aug 01, 07 I was expecting your formula to be
returning a result of 1 upon the ending date being set at even Aug 02, 07 and
similarly a result of 2 upon ending date of Sep. 2, 2007, i.e. a difference
of 1 month and a 1 day being rounded up.

However, upon even dates like (beg.) Aug 01, 2007 and (end.) Sept. 30 a
difference of only 1 month is being returned by your formula instead of 2.

Similarly Aug. 1 & Aug. 2 dates with a difference of 1 day is showing 0
months' difference.

I want to calculate the number of months between two dates.
[....]
I want to Round Up the difference upon a fractional month i.e.
even 1 day difference returns a 1 month difference

=month(a2)-month(a1)+12*(year(a2)-year(a1))

 

[Guest]

Hey Guys,

Simple solution I found myself!!!

=if(day(A1)=day(A2),datedif(a1,a2,"m"),datedif(a1,a2,"m")+1)

Thanx anyway for all your help!!!