Converting date in excel

Question

I need to convert the below date format in excel.

Currently I have: Fri Jan 06 05:10:31 2017

Current Format : ddd MMM dd hh:mm:ss yyyy

I wanted to be in the following format: dd/mm/yyyy hh:mm:ss

Answer 1

You need to remove the day from the string, convert it to a number then format the way you want.

To do the first two steps use this formula:

=--MID(A1,5,LEN(A1))

The third is a custom format:

---

As per the comment:

Answer 2

If you are running into trouble with regional DMY vs. MDY system settings, parse it out longhand so no interpretation is performed; i.e. give the conversion no options.

=DATEVALUE(REPLACE(MID(A2, 4, LEN(A2)), 8, 9, ","))+TIMEVALUE(MID(A2, 12, 8))

Answer 3

Yet another approach:

=DATEVALUE(MID(A1,9,2)&MID(A1,5,3)&RIGHT(A1,4))

Answer 4

You can format it and everything from the formula bar (no need to go in and set formatting).

=TEXT(MID(A1,5,LEN(A1)),"dd/mm/yyyy hh:mm:ss")