Function - HELP!

  • Thread starter Thread starter Noclueatall
  • Start date Start date
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!
 
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
 
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....)
 
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
 
Or even:
=TEXT(a1,"dd/mm")&"/03"

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