date formula

[Laura228]

Hi,

I'm new here and am wondering if anyone can help me with a formula to determine a date 9 months from the date in a cell. For example, if I have a date such as 9/20/08 in cell D3, in cell E3 I'd like to enter a formula to automatically calculate the date 9 months from then. I need to have it take the calendar year into consideration, so that it will display 6/20/09, not 6/20 or 6/20/08.

Thanks in advance for any help you can give.

Laura

[arthurbr]

Hi and welcome to the board
Try =DATE(YEAR(A1), MONTH(A1)+9, DAY(A1))
That is if your starting date is in A1

HTH

[martindwilson]

function EDATE
=EDATE(TODAY(),9)
or =EDATE(A1,9)
where first value is date, second value is number of months away you want
i think this requires the analysis tool pak activated,
go to tools/add-ins and check the analysis tool pak

[shg]

Both good, but different results for some dates, e.g., 9 months from 5/30.

[Laura228]

Thank you, Arthur and Martin, for your quick responses. I tried both suggestions and Arthur's is working for me. I get a #NAME? with Martin's formula. Perhaps that is because I only have Excel 2002. I appreciate the help with this issue. It's going to save me so much time!

[martindwilson]

yep it looks like edate has a problem with
29/05/2008
30/05/2008
31/05/2008
all return 28-Feb-09

[shg]

I don't think it's a problem; it could be what you want on one occasion, and the other on another ...

[martindwilson]

it only happens when the month in the future has less days than your start month so i suppose it just returns end of month as its closest within that month.

[daddylonglegs]

IF you use

=DATE(YEAR(A1), MONTH(A1)+9, DAY(A1))

and A1 is 31st May 2008 then you get 3rd March 2009. I'd think that 28th February would normally be more acceptable.

To get the same result as EDATE without using Analysis ToolPak functions

=MIN(DATE(YEAR(A1), MONTH(A1)+9+{0,1}, DAY(A1)*{1,0}))

[rwgrietveld]

daddylonglegs,

You use the zero (0) in DATE(2008;X;0) to give the last day of the previous month. What is the logic behind this. Is it an anomaly of the DATE function, is it meant to be like this or is it something you found by trial & error?

I'm always interested in the hidden features. Great solution though!

[daddylonglegs]

It might not be common knowledge, but it is described in Excel Help for DATE function.

Zero or negative numbers can be used for any of the YEAR, MONTH or DAY arguments within DATE function [as long as the resultant date isn't negative in its entirety], e.g.

=DATE(2008,-1,-1)

gives 30th October 2007