Dates from Text

[cloudyMalta]

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

 

[Gary''s Student]

The problem is the "st". If you use:

1 January
in place of:
1st January

then:

=DATEVALUE(A1 & "-" & YEAR(NOW())) works just fine.

 

[Teethless mama]

=--(REPLACE(A1,FIND(" ",A1)-2,2,"")&"-"&YEAR(TODAY()))

 

[cloudyMalta]

Thank you very much for your help

The problem is the "st". If you use:

1 January
in place of:
1st January

then:

=DATEVALUE(A1 & "-" & YEAR(NOW())) works just fine.

 

[T. Valko]

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