Suppose your number is in A1 cell, enter this formula:

=DATE(LEFT($A$1;4);MID($A$1;5;2);MID($A$1;7;2)) +TIME(MID($A$1;9;2);MID($A$1;11;2);MID($A$1;13;2))

This works on data in cell A1:

=DATEVALUE(LEFT(A1,4) & "/" & MID(A1,5,2) & "/" & MID(A1,7,2)) + TIMEVALUE(MID(A1,9,2) & ":" & MID(A1,11,2) & ":" & MID(A1,13,2))

then now that the result is a proper Excel date, (optionally here, you could copy/paste-special, values to convert the result from a formula to a value) format that cell to a date format of your choosing, perhaps even a custom format of eg.

dd/mm/yyyy hh:mm:ss

Split the number by using formatting functions and then use the date functions:

To convert a date stored as text into true date format (necessary to sort on dates properly): Let’s say the date looked something like 20100529. This date is actually 5/29/2010, but right now we would say that it was text in YYYYMMDD format.

The first step to making it a true date is splitting the different pieces of the date apart so you can put it back together properly. Because the date isn’t stored as a proper date, you won’t be able to make the YEAR(), MONTH() and DAY() functions work on the cells like you did in the examples above. You’ll have to use string functions in situations like this.

To extract the year: =left(a2, 4)
To extract the month: =mid(a2, 5, 2) To extract the day: =right(a2, 2)

To put the date back together, use the date() function in Excel. All you need to do is fill in the proper pieces of the following formula with cell references: =date(year, month, day) Since you’ve already pulled that information apart you can easily refer to the cells that contain the year, month and the day like this:

=date(b2, c2, d2) See the results below:

In case you need more tricks, I got this from a free pdf, I found it very useful:
http://passion.io/en/learn/courses/22-excel-hacking-and-dynamic-charts