CODEWITHSUNDEEP
exceldateformat
Kessie
Kessie

Reputation: 11

Change date in excel

I am trying to change the date format of my cells in excel to another date format. Currently it is in this format: apr, 10, 2017 01:58:24 PM. I would like to have it in a normal format like dd-mm-yyyy without the time, but I can not get it to work with the formatting in excel.

Thanks in advance, Kester

Upvotes: 1

Views: 73

Answers (2)

Gary's Student
Gary's Student

Reputation: 96781

With data in A1, in B1 enter:

=DATE(MID(A1,10,4),MATCH(LEFT(A1,3),{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0),MID(A1,6,2))

and apply desired formatting:

enter image description here

(this results in a genuine Excel date that can be used in sorts, calculations, etc.)
(this assumes that the day field is always two digits)
(if your month list is in a language other than English, edit the month list)

Upvotes: 0

Jerry
Jerry

Reputation: 71598

You could use this:

=(MID(A2,FIND(",",A2)+2,FIND(",",SUBSTITUTE(A2,",","@",1))-FIND(",",A2)-2)&"-"&LEFT(A2,FIND(",",A2)-1)&"-"&MID(A2,FIND(",",SUBSTITUTE(A2,",","@",1))+2,LEN(A2)))*1

Which is basically a bit of string manipulation (and some substitution of , to @ to help) to put it in the generic format 'd-m-y h:m:s t', which excel understands, then multiply the string by 1 to force into a number (in this case 42835.58222); which you only have to format as date (important!):

enter image description here

Edit: Per comments, the first comma doesn't actually exist, so the revised formula:

=(MID(A2,FIND(" ",A2)+1,FIND(",",A2)-FIND(" ",A2)-1)&"-"&LEFT(A2,FIND(" ",A2)-1)&"-"&MID(A2,FIND(",",A2)+2,LEN(A2)))*1

Upvotes: 2

Related Questions