Adding .45 months to a date

[Trev]

I have a project estimation worksheet where I am calculating the number of
months remaining, then adding the current date + Months using
=DATE(YEAR(C11),MONTH(C11)+H11,DAY(C11)) where C11 is the current date and
H11 is the number of months expressed at a number. It works fine when the
month field is > 1. When the month field is <1 it totally ignores it and the
result ends up being = the start date. Is this "By Design" or a bug?

 

[Barb Reinhardt]

Can you represent .45 months as days and add that to DAY. I think they need
to be integers however.

 

[Mike H]

Instead of .45 of a month add it to days using the formula in H11

=31*.45

Mike

 

[Trev]

Thanks for the reply. I noticed after the suggestions here that it was only
using the integer of 0, 1, 2 etc.. I removed the month from the equation and
converted to days, and it works good.

 

[Ron Rosenfeld]

I have a project estimation worksheet where I am calculating the number of
months remaining, then adding the current date + Months using
=DATE(YEAR(C11),MONTH(C11)+H11,DAY(C11)) where C11 is the current date and
H11 is the number of months expressed at a number. It works fine when the
month field is > 1. When the month field is <1 it totally ignores it and the
result ends up being = the start date. Is this "By Design" or a bug?

The DATE function seems to be behaving exactly as documented, so I guess you
would call it "By Design". The arguments for the DATE function are defined as
integers.

Month is a positive or negative integer representing the month of the year
from 1 to 12 (January to December).

If month is greater than 12, month adds that number of months to the first
month in the year specified. For example, DATE(2008,14,2) returns the serial
number representing February 2, 2009.
If month is less than 1, month subtracts that number of months plus 1 from the
first month in the year specified. For example, DATE(2008,-3,2) returns the
serial number representing September 2, 2007.

--ron