Excel 2002 : How to eliminate the time value after the date ?

[Mr. Low]

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

 

[bj]

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]

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

 

[bj]

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]

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

 

[bj]

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]

Hello BJ,

I try =clean(trim(A1)) today, but it does not work.

My IT people said that they need to reformat the dates in the reports to
solve this problem.

Thanks anyway.

Low

 

[bj]

if the format is approximately like you showed at the initial entry the
following formula may work

=DATE(VALUE(MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,4)),VALUE(LEFT(A1,FIND("/",A1)-1)),VALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)))

 

[Mr. Low]

Hello BJ,

Your formula looks a bit complicated, but gladly it works pretty well.

All the date in text format was succesfully converted to the normal date
format.

Many thanks

Low