DATE AND TIME CONVERSION HELP

[John T]

I download reports in date/time format 2009.03.10 22:35. I would like to
have the year in one column, the time in one column and date in another.
Preferred would be:

Year mo/da hh:mm
2009 03/10 22:35

Any help is greatly appreciated.

John

 

[Eduardo]

Hi John,
Do Text to column, Fixed with, then click where you want the column to
finish and then click next, next Finish
Hope this help

 

[Shane Devenshire]

Hi,

I don't think you can do this in one step with the Text to Columns command.
Also, if you system date setting does not use period for separators then you
need to take another approach.

Suppose the entries are in column A starting in A1:

To get the entire date in C1 enter
=--LEFT(SUBSTITUTE(A1,".","-"),10)
and format as a date
To get the time in D1 enter
=--MID(A1,12,5)
and format as a time
to get the year in E1 enter
=YEAR(C1)