DATE FORMULA

[Megan]

hello you clever peops - one day I may get the hang of this but today is not
the day.

my spr is set out as so:

contrct no start date no of months end date

301856 21/11/07 3

what i need to do is creat a formula that calclulates the end date - so
essentially its the start date x 3 months

can anyone help me?

 

[Sloth]

=DATE(YEAR(B2),MONTH(B2)+3,DAY(B2))

assuming the date is in B2, also assuming you meant +3 months.

 

[Kevin B]

The following formula will work for you and the example assumes that the
starting date is in cell B2:

=DATE(YEAR(B2),MONTH(B2)+3,DAY(B2))

 

[Stephen]

hello you clever peops - one day I may get the hang of this but today is
not
the day.

my spr is set out as so:

contrct no start date no of months end date

301856 21/11/07 3

what i need to do is creat a formula that calclulates the end date - so
essentially its the start date x 3 months

can anyone help me?

I assume you mean that the end date is the start date + 3 months.
For a start date in B2 and a "number of months" in C2, use this formula in
D2:
=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))

 

[Ron Rosenfeld]

hello you clever peops - one day I may get the hang of this but today is not
the day.

my spr is set out as so:

contrct no start date no of months end date

301856 21/11/07 3

what i need to do is creat a formula that calclulates the end date - so
essentially its the start date x 3 months

can anyone help me?

The previous formulas will work depending on what you want to happen when
StartDate is at the end of a month.

For example, given:

StartDate 31 Dec 2007
and no of months = 2

The previous formulas will give a result of 2 Mar 2008.

If that is OK, well and good.

However, if you would prefer to not "run over" the end of a month, then the
following formula will do that. With the above data, it will give a result of
29 Feb 2008

=MIN(DATE(YEAR(StartDate),MONTH(StartDate)+NumMonths+{1,0},DAY(StartDate)*{0,1}))

Also, if you have the Analysis Tool Pak installed, or if you have Excel 2007
which includes the ATP functions, you can use this formula:

=EDATE(StartDate,NumMonths)

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