Formula Creation

[aleflore]

I need to create a formula that will provide a result that calculates a date
three years out and another date one year out with the result being the
greater of the two?

12/31/2007 three years out 12/31/2010 or 1/9/2008 one year out 1/9/2009 but
I need the result to give me the answer as displayed above.

 

[Peo Sjoblom]

Use

=MAX(Formula1,Formula2)

to get 3 years will depend on how you want certain dates to display, one way

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

where A1 holds the date, do the same for 1 year by changing +3 to +1

--


Regards,


Peo Sjoblom

 

[aj scott]

Peo,
Is aleflore worrying about the date format? In which case he should
highlight the desired cells (or go to the single cell), invoke Format >
Custom > "m/d/yyyy".
EDATE function makes the adding years a cinch. It works with either a
serial number or date, & returns a serial number, which can be a previously
date-formatted cell.
I hope I didn't misunderstand the question.

aj

 

[Peo Sjoblom]

Wrong word, I didn't mean the formatting, I meant that depending on the
start date like for instance 01/31/2007, if we add one month, should it be
02/28/08 or 03/03/2007? EDATE does the former and the formula I posted the
latter.
Also note that EDATE might not be installed if the OP is using a company PC
since it is part of the Analysis ToolPak.
There is a way of getting the same result as EDATE using regular function

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

--


Regards,


Peo Sjoblom