Calc End Date
 

How do I calculate the end date (for example of a contract) if I have the
start date and # months when the number # is not always a whole integer?

Use a helper cell to round up or round down the number to whole integer...

"OCD Cindy" wrote:

How do I calculate the end date (for example of a contract) if I have the
start date and # months when the number # is not always a whole integer?

On Sun, 18 Sep 2005 13:09:01 -0700, OCD Cindy <OCD
wrote:

How do I calculate the end date (for example of a contract) if I have the
start date and # months when the number # is not always a whole integer?

With your start date in A1, and your # months in B1, try this:

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)+MOD(B1,1)*30)

Understand, though, that because of the varying number of days in months, your
answer will vary depending on conventions you choose to adopt.

The above formula is probably the simplest, but you could drive yourself crazy
:-)) trying to figure out how to handle these issues.

For example, in the above formula, 31 Jan 2005 + 1 month -- 3 March 2005

If you have the Analysis ToolPak installed, then you could use the following
formula, which eliminates the above issues. I have also made the assumption
that the "fractional month" is based on the number of days in the month that
results from adding the integer portion of "# months" to start date:

=edate(A1,B1)+(32-DAY(edate(A1,B1)-
DAY(edate(A1,B1))+32))*MOD(B1,1)

If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron