Excel Time Format Issue

[Shaun]

I thought i would ask this in a new topic, i have imported data that displays like this.

Race 1 - 12:25PM Michael Stewart Plate (1100 METRES)

i am using this code to try and force it to 24 hour time.

=TEXT(TRIM(CLEAN(MID(LEFT(Q14,FIND("M",Q14)+1),FIND("-",Q14)+1,10))),"hh:mm:ss")

But having no luck, any ideas would be helpful.

[walkermac]

There has to be an easier way of doing this (surely?!) but: =TEXT(LEFT(MID(Q14,FIND("-",Q14)+2,FIND("M ",Q14)-FIND("-",Q14)-1),LEN(MID(Q14,FIND("-",Q14)+2,FIND("M ",Q14)-FIND("-",Q14)-1))-2) & " " & RIGHT(MID(Q14,FIND("-",Q14)+2,FIND("M ",Q14)-FIND("-",Q14)-1),2),"[H]:mm")

It's particularly lengthy so that it can handle X:XX and XX:XX format times. Tested with 9:30AM, 10:30AM, 1:30PM and 10:30PM, with the results you would expect.

There are undoubtedly better ways, the two necessary points are:
1 - the format needs to be "[H]:mm" for 24 hour time
2 - the string you send it needs to have a space between the time figures and the AM/PM for it to correctly recognise it

[Shaun]

Thanks, and yes there is.

=TEXT(REPLACE(LEFT(Q14,FIND("M",Q14)-2),1,FIND("-",Q14)+1,"")+0.5*(MID(Q14,FIND("M",Q14)-1,1)="P"),"hh:mm")


Your's was a huge effort, i am not even going to try and understand what all that does,lol

I am, only good with less complex formulas.

[Chrome Prince]

How about text to columns, using delimiters, and find replace AM and then PM with a blank.
Then format that column special h:mm.