Excel Datetime conversion from YYYYMMDDHHMMSS to DD/MM/YYYY HH:MM:SS [duplicate]

Question

An auto generated report contains event times in the format YYYYMMDDHHMMSS. How do I convert this to a more standard format DD/MM/YYYY HH:MM:SS?

Answer 1

A shorter Excel formula solution exists: Excel convert YYYYMMDDhhmmss [14-char text] to Date + Time (fastest way)

Heres is my answer:

If you have this in the cell D4 : 20161230055053 and you want this 12/30/2016 05:50:53, then you need to use this formula

=DATE(LEFT(D4,4),MID(D4,5,2),MID(D4,7,2))+TIME(MID(D4,9,2),MID(D4,11,2),RIGHT(D4,2))

As you can see D4 has the string with the data YYYYMMDDHHMMSS, ans with that formula you can have a real formated date data where:

DATE is the formula that gives you the date using the parameters inside, Year, month and day.

And as any hour is the fraction of a day you can add the value of the hour, to the date using TIME with the parameters Hour, Minute and Second.

All the parameters that DATE and TIME use, is taking from de string YYYYMMDDHHMMSS using the functions, LEFT, RIGHT and MID.