30th January 2016, 03:44 PM
|
|
Member
|
|
Join Date: Nov 2013
Posts: 603
|
|
There has to be an easier way of doing this (surely?!) but:
Code:
=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 |