=--REPLACE(A1,FIND(" ",A1)-2,2,"")

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=--(REPLACE(A1,FIND(" ",A1)-2,2,"")&"-"&YEAR(TODAY()))



"cloudyMalta" wrote:

Hi,

I have a spreadsheet of Public Holidays for different countries. The
dates
are in thye following format
1st January (for New Years Day)
with no YEAR. This format is used as the list can be viewed by people
using
both UK date format (dd/MM/yyyy) and US format (MM/dd/yyyy), and will be
used
for couple of years.
I would like to add the year to it using (Year(NOW()) to get this date
1st
January 2008, and get the day of the week, in this case Tuesday. I know
about
DATEVALUE. The problem I have is getting from 1st January to 01/01/2008.

I tried CONCATENATE("DATECELL", " ", Year(NOW())). This returns 1st
January
2008, but DATEVALUE on this date returns #VALUE.

Any help would be much appreciated.

Thanks and Regards

Claudio