Format "formatless" date field in excel

Question

In Excel I have a column (date field) in the form: daymonthyear hourminutes. An example is: 20042015 0916 (all this in a single cell). How can it be automatically modified to the more commonly format 20/04/2015 09:16?

Answer 1

What you've got is a string and Excel won't be able to interpret as date. You can convert it to a date time value:

=DATE(MID(A1,5,4),MID(A1,3,2),LEFT(A1,2))+TIME(MID(A1,10,2),RIGHT(A1,2),0)