# Conversion of date format in excel (1st JUN 14) ...

#### ArunS

##### New Member
I am tying to convert given date format 1st JUN 14 to the desired 01-06-14

Kindly suggest. I have tried all that I know. Probably I am definite, missing on some approach.

Best Regards,
ArunS

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### mjbeam

##### Well-known Member
There is probably a much better way, but this should work, assuming the date is in cell A1:

Code:
``=CONCATENATE(IF(ISNUMBER(MID(A1,2,1)*1),LEFT(A1,2),CONCATENATE("0",LEFT(A1,1))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1),7)," JAN ","-01-")," FEB ","-02-")," MAR ","-03-")," APR ","-04-")," MAY ","-05-")," JUN ","-06-")," JUL ","-07-")," AUG ","-08-")," SEP ","-09-")," OCT ","-10-")," NOV ","-11-")," DEC ","-12-"))``

Last edited:

#### MarcelBeug

##### Well-known Member
Try

Code:
``=DATEVALUE(SUBSTITUTE(A1,"st",""))``

#### ArunS

##### New Member
Hi mj,

Though the formula is massive, it covers all possible logics instead of first comverting to number and then date format as I desired. Thank you very much for your solution.

BR,
ArunS

There is probably a much better way, but this should work, assuming the date is in cell A1:

Code:
``=CONCATENATE(IF(ISNUMBER(MID(A1,2,1)*1),LEFT(A1,2),CONCATENATE("0",LEFT(A1,1))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1),7)," JAN ","-01-")," FEB ","-02-")," MAR ","-03-")," APR ","-04-")," MAY ","-05-")," JUN ","-06-")," JUL ","-07-")," AUG ","-08-")," SEP ","-09-")," OCT ","-10-")," NOV ","-11-")," DEC ","-12-"))``

#### ArunS

##### New Member
Hi Marcel,

I tried your approach. Its fine. But then I will need to apply another formula or text to columns, in order to convert the number, to a date format. But it definitely supports the purpose.

Thank you,

BR,
ArunS

Try

Code:
``=DATEVALUE(SUBSTITUTE(A1,"st",""))``

#### Rick Rothstein

##### MrExcel MVP
Hi mj,

Though the formula is massive, it covers all possible logics instead of first comverting to number and then date format as I desired. Thank you very much for your solution.
This formula should produce the same output...

=TEXT(0+REPLACE(A1,FIND(" ",A1)-2,2,""),"dd-mm-yy")

#### Rick Rothstein

##### MrExcel MVP
This formula should produce the same output...

=TEXT(0+REPLACE(A1,FIND(" ",A1)-2,2,""),"dd-mm-yy")
Actually, to match all of your current formulas outputs, the above formula needs to be modified to this...

=TEXT(0+IF(ISNUMBER(0+A1),A1,REPLACE(A1,FIND(" ",A1)-2,2,"")),"dd-mm-yy")

#### ArunS

##### New Member
Hi Rick,

Thanks a lot for this logical formula. Really appreciate it!

BR,
ArunS

Actually, to match all of your current formulas outputs, the above formula needs to be modified to this...

=TEXT(0+IF(ISNUMBER(0+A1),A1,REPLACE(A1,FIND(" ",A1)-2,2,"")),"dd-mm-yy")

Replies
1
Views
458
Replies
2
Views
276
Replies
1
Views
283
Replies
3
Views
392
Replies
2
Views
720

1,195,667
Messages
6,011,049
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back