• 412K Members
• 7,886 Online
• 468K Conversations
SOLVED

New Contributor

# 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 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
Solution

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

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

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

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

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

Bill,

Good to hear that it worked for you !!

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies