Forum Discussion
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.
Other events have not times over 60 minutes, and the times show up as 59:49, which is, best as I can tell, hh:mm, but could be mm:ss.
How can I get the 5 character time to match the hh:mm:ss AM, or, vise versa? I've tried all the time formats, General format, and MOD...to no avail.
I have included a sample OneDrive https://1drv.ms/x/s!ArlJqp1ZJxZNhHbGPROzfriAvNVu?e=o0oQPh of the type I need to sort by time. If it gets corrupted, it's replaceable; Helpful formulas can be placed right in the file.
Thanks, in advance.... my first experience in the Excel community.
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!!
3 Replies
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