Need help with creating a formula
 

Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date?

Date 3 mon 30 mon
Example: 1/1/07----- 4/1/07----- 11/1/08

Need help with creating a formula
 

Try =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))
you may need to format the cell to show a date if you see a number like
39173
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Kimberly" wrote in message
...
Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date?

Date 3 mon 30 mon
Example: 1/1/07----- 4/1/07----- 11/1/08

Need help with creating a formula
 

But keep in mind the short months such as Feb if it is your intention to
always have the day the same. For instance, if the start date is 11/30/06,
the formula will produce 3/2/07 instead of 2/30/07 since there is no 2/30/07.

--
Y


"Bernard Liengme" wrote:

Try =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))
you may need to format the cell to show a date if you see a number like
39173
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Kimberly" wrote in message
...
Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date?

Date 3 mon 30 mon
Example: 1/1/07----- 4/1/07----- 11/1/08

Need help with creating a formula
 

Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009.

Another way:

In cell A1: 1/1/07

For 3 months: =EDATE(A1,3)
For 30 months: =EDATE(A1,30)

Like Bernard said format cell ......

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

Epinn

"Kimberly" wrote in message ...
Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date?

Date 3 mon 30 mon
Example: 1/1/07----- 4/1/07----- 11/1/08

Need help with creating a formula
 

Please note the nature of EDATE.

From Excel Help:

Use EDATE to calculate maturity dates or due dates that fall on the **same** day of the month as the date of issue.

Epinn

"Epinn" wrote in message ...
Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009.

Another way:

In cell A1: 1/1/07

For 3 months: =EDATE(A1,3)
For 30 months: =EDATE(A1,30)

Like Bernard said format cell ......

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

Epinn

"Kimberly" wrote in message ...
Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date?

Date 3 mon 30 mon
Example: 1/1/07----- 4/1/07----- 11/1/08

Need help with creating a formula
 

Was not aware of the EDATE function. Thanks.
--
Y


"Epinn" wrote:

Please note the nature of EDATE.

From Excel Help:

Use EDATE to calculate maturity dates or due dates that fall on the **same** day of the month as the date of issue.

Epinn

"Epinn" wrote in message ...
Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009.

Another way:

In cell A1: 1/1/07

For 3 months: =EDATE(A1,3)
For 30 months: =EDATE(A1,30)

Like Bernard said format cell ......

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

Epinn

"Kimberly" wrote in message ...
Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date?

Date 3 mon 30 mon
Example: 1/1/07----- 4/1/07----- 11/1/08

Need help with creating a formula
 

Y, Thanks for your input.

Just verified that EDATE takes care of leap year February as well. I worry too much or I am detailed? <G

A1: 12/31/2007

=EDATE(A1,2) returns 2/29/08 when cell is formatted as date.

Kimberly, please note that Bernard's formula and mine will give different results. Not sure what you want exactly, but I think we have covered two scenarios. Is there a third one?

Epinn

"Yacbo" wrote in message ...
Was not aware of the EDATE function. Thanks.
--
Y


"Epinn" wrote:

Please note the nature of EDATE.

From Excel Help:

Use EDATE to calculate maturity dates or due dates that fall on the **same** day of the month as the date of issue.

Epinn

"Epinn" wrote in message ...
Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009.

Another way:

In cell A1: 1/1/07

For 3 months: =EDATE(A1,3)
For 30 months: =EDATE(A1,30)

Like Bernard said format cell ......

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

Epinn

"Kimberly" wrote in message ...
Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date?

Date 3 mon 30 mon
Example: 1/1/07----- 4/1/07----- 11/1/08

Need help with creating a formula
 

I think my concern about leap year and February is justified and I don't worry too much. Just found something interesting.

http://support.microsoft.com/kb/179583

More importantly, MS has fixed the problem.

This problem no longer occurs in Microsoft Excel 2000. <<

EDATE may not be good for versions prior to 2000??

If I have missed something, please feel free to let us know.

Epinn


"Epinn" wrote in message ...
Y, Thanks for your input.

Just verified that EDATE takes care of leap year February as well. I worry too much or I am detailed? <G

A1: 12/31/2007

=EDATE(A1,2) returns 2/29/08 when cell is formatted as date.

Kimberly, please note that Bernard's formula and mine will give different results. Not sure what you want exactly, but I think we have covered two scenarios. Is there a third one?

Epinn

"Yacbo" wrote in message ...
Was not aware of the EDATE function. Thanks.
--
Y


"Epinn" wrote:

Please note the nature of EDATE.

From Excel Help:

Use EDATE to calculate maturity dates or due dates that fall on the **same** day of the month as the date of issue.

Epinn

"Epinn" wrote in message ...
Regarding your example, I don't think the result for 30 months is 11/1/08. It should be 7/1/2009.

Another way:

In cell A1: 1/1/07

For 3 months: =EDATE(A1,3)
For 30 months: =EDATE(A1,30)

Like Bernard said format cell ......

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

Epinn

"Kimberly" wrote in message ...
Does anyone know how to create a formula that will calculate 3 & 30 months
from any given date?

Date 3 mon 30 mon
Example: 1/1/07----- 4/1/07----- 11/1/08