Custom Cell Category or Other Weird Data Format

G

George Schrader

I have a date time stamp that I imported into excel within some data that I am analyzing. I would like to get excel to recognize it as a time but I have been unable to find a solution. The stamp looks like this 2004031503510430. Reading left to right it states 2004(year), 03(month), 15(day), 03(hour), 51(minute), 04(second), and 30(precise to a tenth of a second). I have been able to separate the date from the time using the LEFT and RIGHT worksheet functions, but getting it formatted as time seems impossible
 
J

Jason Morin

=TEXT(A1,"0000\-00\-00 00\:00\:00\.00")*1

and custom format the cell as:

mm/dd/yy hh:mm:ss.00

HTH
Jason
Atlanta, GA
-----Original Message-----
I have a date time stamp that I imported into excel
Click to expand...
within some data that I am analyzing. I would like to get
excel to recognize it as a time but I have been unable to
find a solution. The stamp looks like this
2004031503510430. Reading left to right it states 2004
(year), 03(month), 15(day), 03(hour), 51(minute), 04
(second), and 30(precise to a tenth of a second). I have
been able to separate the date from the time using the
LEFT and RIGHT worksheet functions, but getting it
formatted as time seems impossible.
 
G

George Schrader

Appreciate the help, but that didn't work. Changing the custom cell format gave me the #'s and using the worksheet function on 2004031503520240 gave me 38061. Any other ideas.
 
J

Jason Morin

It worked for me.
-----Original Message-----
Appreciate the help, but that didn't work. Changing the
Click to expand...
custom cell format gave me the #'s and using the worksheet
function on 2004031503520240 gave me 38061. Any other
ideas.
 
Top