CODEWITHSUNDEEP
excel-formula
user8363385
user8363385

Reputation: 1

Excel: text conversion to date format

I would like to convert text - Apr 7 2017 into date format on Excel spread sheet. Is there a formula that might help with it? I have already tried format cell into date format but this did not change the cell properties. Thank you for your suggestions

Upvotes: 0

Views: 88

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

With data in A1, in another cell enter:

=DATEVALUE(LOOKUP(LEFT(A1,3),{"Apr","Aug","Dec","Feb","Jan","Jul","Jun","Mar","May","Nov","Oct","Sep"},{4,8,12,2,1,7,6,3,5,11,10,9}) & MID(SUBSTITUTE(A1," ","/"),4,99))

enter image description here

An alternative:

=DATEVALUE(TRIM(MID(A1,5,2)) & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))

Upvotes: 0

Related Questions