SOLVED

How to convert 12:59:49 AM (HH:MM:SS AM) to 59:49 (MM:SS)

Copper Contributor

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.

3 Replies
best response confirmed by mrbill47 (Copper Contributor)
Solution

@mrbill47 

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!!

@Kodipady 

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

@mrbill47 

Bill,

Good to hear that it worked for you !! 

1 best response

Accepted Solutions
best response confirmed by mrbill47 (Copper Contributor)
Solution

@mrbill47 

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!!

View solution in original post