Dear Sir,

I run a report using another business system in Excel Reader Format. I
copied the report and pasted it to an Excel 2002 Worksheet.

Under the date column, I have a block of date that shows the time as well.

I need to eliminate the time value after the date as it takes up to much
space.

I tried to Right Click Format Cells Date *14/03/2001 but it did
not work.

All other date format also did not work. The cells are not protected cells.

Could you please show how to do the conversion as illustrated below ?

Before conversion After conversion
Date Date

8/25/2005 12:00:00AM 8/25/2005

8/25/2005 12:00:00AM 8/25/2005

4/15/2002 12:00:00AM 4/15/2002

12/3/2003 12:00:00AM


Thanks

Low




Thanks



Low


--
A36B58K641

Sounds like the data is in text format
make sure there are empty cells to the right of your data set
select the date time data
Data-Text to column
delimited
next
select space
next
date MDY
finish
clear the time cells

"Mr. Low" wrote:

Dear Sir,

I run a report using another business system in Excel Reader Format. I
copied the report and pasted it to an Excel 2002 Worksheet.

Under the date column, I have a block of date that shows the time as well.

I need to eliminate the time value after the date as it takes up to much
space.

I tried to Right Click Format Cells Date *14/03/2001 but it did
not work.

All other date format also did not work. The cells are not protected cells.

Could you please show how to do the conversion as illustrated below ?

Before conversion After conversion
Date Date

8/25/2005 12:00:00AM 8/25/2005

8/25/2005 12:00:00AM 8/25/2005

4/15/2002 12:00:00AM 4/15/2002

12/3/2003 12:00:00AM


Thanks

Low




Thanks



Low


--
A36B58K641

Hello BJ,

Yes, Your steps works. The time clock has been removed.

However the date still remain as text. It could not be change into other
formats such as Aug-25,2005 or 25/08/2005 etc.

Any method for converting it to date values ?

Thanks

Low

--
A36B58K641


"bj" wrote:

Sounds like the data is in text format
make sure there are empty cells to the right of your data set
select the date time data
Data-Text to column
delimited
next
select space
next
date MDY
finish
clear the time cells

"Mr. Low" wrote:

Dear Sir,

I run a report using another business system in Excel Reader Format. I
copied the report and pasted it to an Excel 2002 Worksheet.

Under the date column, I have a block of date that shows the time as well.

I need to eliminate the time value after the date as it takes up to much
space.

I tried to Right Click Format Cells Date *14/03/2001 but it did
not work.

All other date format also did not work. The cells are not protected cells.

Could you please show how to do the conversion as illustrated below ?

Before conversion After conversion
Date Date

8/25/2005 12:00:00AM 8/25/2005

8/25/2005 12:00:00AM 8/25/2005

4/15/2002 12:00:00AM 4/15/2002

12/3/2003 12:00:00AM


Thanks

Low




Thanks



Low


--
A36B58K641

if I do it and format-cell-number, I can set the format as I wish.
I don't know why yours wouldn't

"Mr. Low" wrote:

Hello BJ,

Yes, Your steps works. The time clock has been removed.

However the date still remain as text. It could not be change into other
formats such as Aug-25,2005 or 25/08/2005 etc.

Any method for converting it to date values ?

Thanks

Low

--
A36B58K641


"bj" wrote:

Sounds like the data is in text format
make sure there are empty cells to the right of your data set
select the date time data
Data-Text to column
delimited
next
select space
next
date MDY
finish
clear the time cells

"Mr. Low" wrote:

Dear Sir,

I run a report using another business system in Excel Reader Format. I
copied the report and pasted it to an Excel 2002 Worksheet.

Under the date column, I have a block of date that shows the time as well.

I need to eliminate the time value after the date as it takes up to much
space.

I tried to Right Click Format Cells Date *14/03/2001 but it did
not work.

All other date format also did not work. The cells are not protected cells.

Could you please show how to do the conversion as illustrated below ?

Before conversion After conversion
Date Date

8/25/2005 12:00:00AM 8/25/2005

8/25/2005 12:00:00AM 8/25/2005

4/15/2002 12:00:00AM 4/15/2002

12/3/2003 12:00:00AM


Thanks

Low




Thanks



Low


--
A36B58K641

Hello BJ,

I tried again today. Some date can be converted to date values but some don't.

I am a bit puzzle. I would check with our IT people on this.

Thank you anyway.

LOw

--
A36B58K641


"bj" wrote:

if I do it and format-cell-number, I can set the format as I wish.
I don't know why yours wouldn't

"Mr. Low" wrote:

Hello BJ,

Yes, Your steps works. The time clock has been removed.

However the date still remain as text. It could not be change into other
formats such as Aug-25,2005 or 25/08/2005 etc.

Any method for converting it to date values ?

Thanks

Low

--
A36B58K641


"bj" wrote:

Sounds like the data is in text format
make sure there are empty cells to the right of your data set
select the date time data
Data-Text to column
delimited
next
select space
next
date MDY
finish
clear the time cells

"Mr. Low" wrote:

Dear Sir,

I run a report using another business system in Excel Reader Format. I
copied the report and pasted it to an Excel 2002 Worksheet.

Under the date column, I have a block of date that shows the time as well.

I need to eliminate the time value after the date as it takes up to much
space.

I tried to Right Click Format Cells Date *14/03/2001 but it did
not work.

All other date format also did not work. The cells are not protected cells.

Could you please show how to do the conversion as illustrated below ?

Before conversion After conversion
Date Date

8/25/2005 12:00:00AM 8/25/2005

8/25/2005 12:00:00AM 8/25/2005

4/15/2002 12:00:00AM 4/15/2002

12/3/2003 12:00:00AM


Thanks

Low




Thanks



Low


--
A36B58K641

There is a possibilty that depending here it comes from, there could be non
printing characterswhich you can't see.
the Trim() and or Clean() function may be needed as a secondary factor
before it can be recognized as a date.
try a helper column with =clean(trim(A1))
correct column and first row as needed)
copy and paste special values
try the text to column again with date selected



"Mr. Low" wrote:

Hello BJ,

I tried again today. Some date can be converted to date values but some don't.

I am a bit puzzle. I would check with our IT people on this.

Thank you anyway.

LOw

--
A36B58K641


"bj" wrote:

if I do it and format-cell-number, I can set the format as I wish.
I don't know why yours wouldn't

"Mr. Low" wrote:

Hello BJ,

Yes, Your steps works. The time clock has been removed.

However the date still remain as text. It could not be change into other
formats such as Aug-25,2005 or 25/08/2005 etc.

Any method for converting it to date values ?

Thanks

Low

--
A36B58K641


"bj" wrote:

Sounds like the data is in text format
make sure there are empty cells to the right of your data set
select the date time data
Data-Text to column
delimited
next
select space
next
date MDY
finish
clear the time cells

"Mr. Low" wrote:

Dear Sir,

I run a report using another business system in Excel Reader Format. I
copied the report and pasted it to an Excel 2002 Worksheet.

Under the date column, I have a block of date that shows the time as well.

I need to eliminate the time value after the date as it takes up to much
space.

I tried to Right Click Format Cells Date *14/03/2001 but it did
not work.

All other date format also did not work. The cells are not protected cells.

Could you please show how to do the conversion as illustrated below ?

Before conversion After conversion
Date Date

8/25/2005 12:00:00AM 8/25/2005

8/25/2005 12:00:00AM 8/25/2005

4/15/2002 12:00:00AM 4/15/2002

12/3/2003 12:00:00AM


Thanks

Low




Thanks



Low


--
A36B58K641