System Date.Time conversion in Excel

How do I convert this system generated DATE.TIME format in Excel into Date and Time (much more readable to a user)?

2111031439

i.e. yy/mm/dd:hr/mm

Date time should be:

21 11 03 14:39

YY MM DD HR:MM

Many thanks

Solution 1:

If the number is an integer, and assuming it is in the desired time zone and in this century, you can convert it with the formula:

=--TEXT(A1,"\2\000\/00\/00 00\:00")

This will result in an Excel Date/Time and appear as something like 44503.61042. You can then format the cell to whatever date format you want.

If the number is a "real date" that happens to look as it does due to formatting, then merely change the formatting.