Process a Time Stamp

[david.decastro]

Hi I am looking to convert a timestamp in a file such that it is read
as a time in excel.

Currently it comes in general format for example:2006032211272800 which
is in yyyy-mm-dd-hh-mm(mins)-ss-00. That is year 22/03/2006 at
11:27:28. The time is 24 hour clock and minutes and seconds roll over
at 60 i.e. the time is read as a clock not decimal places. Note the
last two digits are always blank.

The field can easily be converted to a number and divided by 100 to
exclude the last 2 digits.

Is there an easy way in which this time stamp can be read by excel such
that it is easy to recognise the time elapsed between enteries?

Examples of sequential timestamps are as below:

Cell B1: 2006032211272800
Cell B2: 2006032211273100
Cell B3: 2006032211282100
Cell B4: 2006032211283800
Cell B5: 2006032211285000

Cheers

David

 

[Dave Peterson]

I'd use another column with a formula like:

=--TEXT(LEFT(A1,14),"0000\/00\/00 00\:00\:00")

And format it nicely:
dd/mm/yyyy hh:mm:ss
(or what you like)

 

[Guest]

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))

Format cell as: dd/mm/yyyy hh:mm:ss