Change the date and time format to show just the date

[Tmoske]

I am exporting data from a database to excel and the date format in one of the colomns is as follows: <TABLE style="WIDTH: 125pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=166 border=0 x:str><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 125pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" width=166 height=17>2009-03-08 2203 Z</TD></TR></TBODY></TABLE>

I tried "format cells" but it won't allow me to change the format of this cell.

I have a lot of data, several thousand rows that I need to filter by date. As you can probably imagine it makes it quite hard if the time is included in the date.

I have an idea but don't know how to make it work. I tried to use the "Replace" under "Edit" to remove the time from each cell, but could not get the formating right.

Thanks,
Tmoske

 

[bobinnc]

Tmoske,

Select the cells in the column and then do Menu "Data", "Text to Columns", "Fixed Width", <Next>, ensure there is a line after the end of the date, <Next>, Mark all columns after the first one as "Do not import (skip), then <Finished>. It should convert the column of data to dates. Good luck. - Bob

 

[gardnertoo]

In another column, this formula converts your sample value to date:

=DATEVALUE(LEFT(A1,10))

Edit: right after I posted, I see bobinnc's post. His solution is better, use that.

 

[Tmoske]

Thanks so much. That method works great!!!

Tmoske