CODEWITHSUNDEEP
excel
Paolo Ngalongalo
Paolo Ngalongalo

Reputation: 11

Convert text to dates in excel

I've done all of I can in excel to extract the date from this text "Mon Nov 03 10:07:06 2014" Can anyone help me regarding with this? I want to get the date in this form e.g 11/3/2014 and once I click format cells I will be able to change its date format.

Thanks in advance.

Upvotes: 1

Views: 93

Answers (2)

ab_ever
ab_ever

Reputation: 66

you could work with left and right to extract the values. Afterwards you can convert it to date with date. here is a solution, maybe not the best:

=DATE(RIGHT(H47;4);MONTH("1"&RIGHT(LEFT(H47;7);3));RIGHT(LEFT(H47;10);2))

cell H47 = "Mon Nov 03 10:07:06 2014"

result = 03.11.2014

I hope it helps.

best - AB

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

With data in A1, in another cell enter:

=DATEVALUE(MID(A1,8,3)&"-"&MID(A1,5,3)&"-"&RIGHT(A1,4))

and format this cell as Date:

enter image description here

The key is to create a string that Datevalue() will accept!

Upvotes: 1

Related Questions