=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-"))
=DATEVALUE(SUBSTITUTE(A1,"st",""))
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-"))
Try
Code:=DATEVALUE(SUBSTITUTE(A1,"st",""))
This formula should produce the same output...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.
Actually, to match all of your current formulas outputs, the above formula needs to be modified to this...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")