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

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?

Solution

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.

Comments

[Elbert Villarreal]

You post a question and answer immediately... do you want a answer yet???

[Elbert Villarreal]

This answer gives you a String not a date time value, if you want to format or manipulate any how that result would be a mess, instead please see my answer.

[ajthewebdev]

I was able to custom format the resulting cell to dd/mm/yyyy hh:mm:ss, thanks