Function - HELP!

N

Noclueatall

If anyone could help me with the following I would appreciate it.

I have one column with a date in it such as :

Column 1 Column 2

10/27/89 10/27/03

I want column 2 to be automatically generated with the same month and
date but with the current year. Is there any way to do this in excel?
Thanks in advance!
 
F

Freemini

Assuming your base date is in A1 and your current date is in B1
if you enter the following in B1
=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))
this will update to the current year but retain the original day and
month. [you can also use NOW() instead of TODAY()]

Hope this what you are looking for

Mike
 
C

ChrisBat

If you were going to be using the spreadsheet only for this year, or can
easily change the formula for next year, would

=LEFT(A1,5)&"/03"

work just as well? (i'm procrastinating at work....)
 
F

Freemini

Using =LEFT(A1,5)&"/03" results in 36095/03 because the date in A1 is
not text.

To utilise the LEFT function the formula would have to be:

=LEFT(TEXT(A1,"DD/MM/YY"),5)&"/03"

this first converts the date to text and then performs the left
function.

Mike
 
D

Dave Peterson

Or even:
=TEXT(a1,"dd/mm")&"/03"

(but I like your first formula so that the value is still a date.)
 
Top