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?

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

HTH


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=554705

How about this:

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

HTH,
Elkar


"Jessica" wrote:

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?

=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.

"Jessica" wrote:

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?

On Thu, 22 Jun 2006 12:01:03 -0700, bpeltzer
wrote:

=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.

"Jessica" wrote:

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?

=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

Hi Jessica,

How bout = C5+365

HTH
Martin

MartinW wrote:
How bout = C5+365

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.

Jessica wrote:
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.

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)))

On Thu, 22 Jun 2006 11:26:02 -0700, Jessica
wrote:

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?

=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

Nel post
*Ron Rosenfeld* ha scritto:

On Thu, 22 Jun 2006 12:01:03 -0700, bpeltzer
wrote:

=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.

"Jessica" wrote:

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?

=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

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

On Fri, 23 Jun 2006 13:13:09 +0200, "Franz Verga" wrote:

Nel post
*Ron Rosenfeld* ha scritto:

On Thu, 22 Jun 2006 12:01:03 -0700, bpeltzer
wrote:

=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.

"Jessica" wrote:

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?

=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

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.

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

Jessica Wrote:
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?

If you don't want to use EDATE then

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=554705

Hi Everyone,

Thank you for your replies!!!

~Jessica

"Jessica" wrote:

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?