Aug 20 2019 09:25 PM
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 editable file 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.
Aug 20 2019 11:00 PM - edited Aug 20 2019 11:13 PM
SolutionYou 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!!
Aug 22 2019 11:48 AM
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
Aug 22 2019 10:58 PM
Aug 20 2019 11:00 PM - edited Aug 20 2019 11:13 PM
SolutionYou 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!!