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

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

1 January
in place of:
1st January

then:

=DATEVALUE(A1 & "-" & YEAR(NOW())) works just fine.
--
Gary''s Student - gsnu200812


"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

=--(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

Thank you very much for your help

"Gary''s Student" wrote:

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

1 January
in place of:
1st January

then:

=DATEVALUE(A1 & "-" & YEAR(NOW())) works just fine.
--
Gary''s Student - gsnu200812


"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

=--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