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

[ArunS]

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

 

[mjbeam]

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

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

 

[MarcelBeug]

Try

=DATEVALUE(SUBSTITUTE(A1,"st",""))

 

[ArunS]

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:

=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]

ADVERTISEMENT

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

=DATEVALUE(SUBSTITUTE(A1,"st",""))

 

[Rick Rothstein]

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]

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]

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