Forum Discussion
Solved
How to convert 12:59:49 AM (HH:MM:SS AM) to 59:49 (MM:SS)
I'm trying to sort race results, over a number of years, by time. Some of the events had times over an hour, and the time format is 12:59.49 AM for time under one hour and 1:12:12 AM over one hour. ...
You can check this formula in available in column E and F(RK.Solution1 and 2).
=IF(LEN(C3)=5,TIME(0,LEFT(C3,2),RIGHT(C3,2)),C3) - converts 5 char in to MI:SS
=IF(LEN(C3)>5,TIME(MINUTE(C3),SECOND(C3),0),"") - converts MI:SS format to HH:MI
You might want check for more examples input.
Hope it helps!!
Thanks for the help. I converted Col. E, and it worked on all the data in the column. I didn't mess with Col. F, as dividing Col. E by 4 worked to get an accurate pace (that matched Col. F, only with more accuracy)..
I am ecstatic. Would have taken me days, IF I ever got t right!
Thanks, again for the help.
Bill