Date plus 1 year (february problem)

J

Jessica

Hi Everyone,

I want to compute date + 1 year, I am using the following:
=DATE(YEAR(C5)+1,MONTH(C5),DAY(C5))

My problem is when C5 is 2-29-2004, I get a result of 3-1-2005, when I want
2-28-2005.

I've tried playing around with my formula for a date minus 3 month's post
but can't adapt it.
=MIN(DATE(YEAR(C5),MONTH(C5)+{-2,-3},DAY(C5)*{0,1}))

Any ideas?
 
B

Bearacade

=if(or(mod(year(c5),400)=0,and(mod(year(c5),4)=0,mod(year(c5),100)<>0)),date(year(c5)+1,month(c5),day(c5))-1,date(year(c5)+1,month(c5),day(c5)))

HTH
 
E

Elkar

How about this:

=DATE(YEAR(C5)+1,MONTH(C5),IF(AND(MONTH(C5)=2,DAY(C5)=29),DAY(C5)-1,DAY(C5)))

HTH,
Elkar
 
B

bpeltzer

=MIN(DATE(YEAR(C5)+1,MONTH(C5),DAY(C5)),DATE(YEAR(C5)+1,MONTH(C5)+1,0))
This advances one year, but not beyond the end of the current month in the
next year.
 
R

Ron Rosenfeld

=MIN(DATE(YEAR(C5)+1,MONTH(C5),DAY(C5)),DATE(YEAR(C5)+1,MONTH(C5)+1,0))
This advances one year, but not beyond the end of the current month in the
next year.
Click to expand...

=EDATE(C5,12)

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
 
J

joeu2004

MartinW said:
How bout = C5+365
Click to expand...

If C5 contains 2/28/2004, then C5+365 is 2/27/2005. In fact, that
off-by-one error arises with any date on or before 2/29/2004, e.g.
1/30/2004. Probably not the desired result with any date except
perhaps 2/29/2004.
 
J

joeu2004

Jessica said:
I want to compute date + 1 year, I am using the following:
=DATE(YEAR(C5)+1,MONTH(C5),DAY(C5))

My problem is when C5 is 2-29-2004, I get a result of 3-1-2005, when I want
2-28-2005.
Click to expand...

At the risk of providing an inelegant-but-KISS solution, why not
simply:

=if(C5 = date(year(C5),2,29), date(1+year(c5),2,28),
date(1+year(c5),month(c5),day(c5)))
 
R

Ron Rosenfeld

Hi Everyone,

I want to compute date + 1 year, I am using the following:
=DATE(YEAR(C5)+1,MONTH(C5),DAY(C5))

My problem is when C5 is 2-29-2004, I get a result of 3-1-2005, when I want
2-28-2005.

I've tried playing around with my formula for a date minus 3 month's post
but can't adapt it.
=MIN(DATE(YEAR(C5),MONTH(C5)+{-2,-3},DAY(C5)*{0,1}))

Any ideas?
Click to expand...

=EDATE(C5,12)

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
 
F

Franz Verga

Nel post *Ron Rosenfeld* ha scritto:
=EDATE(C5,12)

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
Click to expand...

But if you have to share your workbook with someone else, moreover in an
international enviroment, I think it should be better not to use function on
ATP, for two main reasons:
1) other people may not have the ATP installed;
2) other people may have localized version of Excel and the ATP function are
not translated: so if you open from a localized version of Excel a file in
which you used the EDATE function, it can be read, because localized version
read the English version, but viceversa is not true.
So, if you open a file made with an Italian version of Excel in which is
used the DATA.MESE (=EDATE) function, as worksheet recalculate you will have
an #NAME! error.

For these reasons I think it should be better to use regular Excel function,
instead of ATP function.

--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
R

Ron Rosenfeld

Nel post *Ron Rosenfeld* ha scritto:


But if you have to share your workbook with someone else, moreover in an
international enviroment, I think it should be better not to use function on
ATP, for two main reasons:
1) other people may not have the ATP installed;
2) other people may have localized version of Excel and the ATP function are
not translated: so if you open from a localized version of Excel a file in
which you used the EDATE function, it can be read, because localized version
read the English version, but viceversa is not true.
So, if you open a file made with an Italian version of Excel in which is
used the DATA.MESE (=EDATE) function, as worksheet recalculate you will have
an #NAME! error.

For these reasons I think it should be better to use regular Excel function,
instead of ATP function.
Click to expand...

Non-ATP functions have already been proferred. If the OP is running an English
version of Excel, the ATP function is simpler and, since the ATP is not only
provided with the older versions of Excel, but also will have its functions
firmly integrated as a built-in in the newer versions of Excel, it seems
reasonable to me to offer it as one of several solutions.


--ron
 
D

daddylonglegs

Jessica said:
Hi Everyone,

I want to compute date + 1 year, I am using the following:
=DATE(YEAR(C5)+1,MONTH(C5),DAY(C5))

My problem is when C5 is 2-29-2004, I get a result of 3-1-2005, when I
want
2-28-2005.

I've tried playing around with my formula for a date minus 3 month's
post
but can't adapt it.
=MIN(DATE(YEAR(C5),MONTH(C5)+{-2,-3},DAY(C5)*{0,1}))

Any ideas?
Click to expand...

If you don't want to use EDATE then

=MIN(DATE(YEAR(C5)+1,MONTH(C5)+{0,1},DAY(C5)*{1,0}))
 
Top