Transform Text:2015/01/01 01:03:15 PM EST INTO DATE "01/01/2015"

[yomero]

Hi,

I have to clean up a table.
The records were imported as text and are showing in 2 wasy:
- 42238
- 2015/01/01 01:03:15 PM EST

how can I convert them to a proper date column?
- DD/MM/YYY

Thanks!

 

[cooper645]

Just entered 42238 in a cell, then changed the cell format to a date, which returned the date 22/08/2015
the 42238 is the serial number of the date since 1 Jan 1990, so 42239 is the 23/08/2015

as for the second one with the time, I'm not entirely sure on your options.

 

[alansidman]

Here is a tutorial on manipulating data

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

 

[Micron]

Depending on the structure of your date/time values, there are a few ways to do this.
If the dates will always be 10 digits, you could simply extract 10 characters from the left: Left(yourvalue,10). I presume #'s will not be part of your date.

If not, you need to determine the length of the entire date (remember for this suggestion the date would sometimes be 01/01/2015 and sometimes 1/1/2015 or even 1/1/15 [not good]). So from 26 (2015/01/01 01:03:15 PM EST) you subtract 16 (the " 01:03:15 PM EST" portion), thus running the function: Left(Len(yourDate)-16).

If you want a format that differs from the value you have, or different from your system settings
(e.g. dd/mm/yyyy vs yyyy,mm/dd) you'll have to wrap the example within the Format function.
Maybe as: Format(Left(Len(yourDate)-16),"mm/dd/yyyy")

For the date serial numbers, Format(42238, "dd/mm/yyyy") will return 22/08/2015 as noted by cooper645