Date and Time Format

[babelfish]

Hi,

I am trying to view some Datalog files downloaded from a device in a .txt format we have that then need to delimit. The Date and Time format however when it comes off is in the following format
20180711140751

where it looks to be YYYYMMDDHHMMSS

I am struggling to find how to reformat this into a usuable and readable Date and time format.

The delimited text example is below before importing to excel
VAL;20180711172312;0.00
EVE;20180711172418;Switch Instr Off (Instrument Channel)

Any help appreciated.

Cheers
Graeme

 

[Joe4]

Let's say that your entry is a text entry in cell A4.
Here is a formula that will convert it to a valid date/time entry:

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

 

[babelfish]

Perfect, thank you

 

[Joe4]

You are welcome!