CODEWITHSUNDEEP
exceldate
Annalise Azzopardi
Annalise Azzopardi

Reputation: 133

Converting date in excel

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

Upvotes: 0

Views: 304

Answers (4)

user4039065
user4039065

Reputation:

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))

enter image description here

Upvotes: 1

G42
G42

Reputation: 10019

Yet another approach:

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

Upvotes: 1

billyhoes
billyhoes

Reputation: 346

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")

enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

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))

enter image description here

The third is a custom format:

enter image description here

As per the comment:

enter image description here

Upvotes: 2

Related Questions