Calculating dates

[Jordon]

Can anyone tell me what I should use (in the way of helper cells)
to take any date (mm/dd/yyyy) and turn it into that same month
and day for specific year? For instance, turn...

10/12/2009 into 10/12/2010
and
4/6/1998 into 4/6/2010

I'm trying to determine anniversary date based on start date and
do it starting in 2010.

TIA

 

[Pete_UK]

Try this:

=DATE(2010,MONTH(A1),DAY(A1))

Assuming your date is in A1.

Hope this helps.

Pete

 

[Jordon]

Wow. Easier than I thought. Thanks Pete!

Try this:

=DATE(2010,MONTH(A1),DAY(A1))

Assuming your date is in A1.

Hope this helps.

Pete

 

[Stan Brown]

Can anyone tell me what I should use (in the way of helper cells)
to take any date (mm/dd/yyyy) and turn it into that same month
and day for specific year? For instance, turn...

10/12/2009 into 10/12/2010
and
4/6/1998 into 4/6/2010

I'm trying to determine anniversary date based on start date and
do it starting in 2010.

Suppose the original date is in A1 and the desired year is in B1.
Then your formula is

=DATE( B1, MONTH(A1), DAY(A1) )

 

[Pete_UK]

You're welcome, Jordon - thanks for feeding back.

Pete

 

[Rick Rothstein]

Another way involving only one function call...

=--TEXT(A5,"m/d/""2010""")

Rick Rothstein (MVP - Excel)




"Jordon" wrote in message
Can anyone tell me what I should use (in the way of helper cells)
to take any date (mm/dd/yyyy) and turn it into that same month
and day for specific year? For instance, turn...

10/12/2009 into 10/12/2010
and
4/6/1998 into 4/6/2010

I'm trying to determine anniversary date based on start date and
do it starting in 2010.

TIA

 

[joeu2004]

Can anyone tell me what I should use (in the way of helper cells)
to take any date (mm/dd/yyyy) and turn it into that same month
and day for specific year? For instance, turn...
10/12/2009 into 10/12/2010
and
4/6/1998 into 4/6/2010
I'm trying to determine anniversary date based on start date and
do it starting in 2010.

Depends on what you want to do with a start date of 2/29/2008.

If you are content with an anniversary date of 3/1/2010, then
DATE(2010,MONTH(A1),DAY(A1)) might be the best solution.

But most people prefer an anniversary date of 2/28/2010. In that
case, I would suggest EDATE(A1,12*(2010-YEAR(A1)) formatted as Date.

If you do not explicitly select a Date format, Excel will display a
number, which represents the number of days since 12/31/1899 unless
you choose the 1904 Date calculation option -- not recommended unless
you are creating a file on or for Mac.

If you are using XL2003 and you get a #NAME error, see the EDATE help
page regarding enabling the ATP. If you cannot use the ATP, there is
an alternative formulation, but it is much more complex.

 

[joeu2004]

Another way involving only one function call...
=--TEXT(A5,"m/d/""2010""")

Does not work at all if A5 contains the date 2/29/2008.

See my other response for alternatives for dealing with leap days.