SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-814195%22%20slang%3D%22en-US%22%3EHow%20to%20convert%2012%3A59%3A49%20AM%20(HH%3AMM%3ASS%20AM)%20to%2059%3A49%20(MM%3ASS)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814195%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20sort%20race%20results%2C%20over%20a%20number%20of%20years%2C%20by%20time.%3C%2FP%3E%3CP%3ESome%20of%20the%20events%20had%20times%20over%20an%20hour%2C%20and%20the%20time%20format%20is%2012%3A59.49%20AM%20for%20time%20under%20one%20hour%20and%26nbsp%3B1%3A12%3A12%20AM%20over%20one%20hour.%3C%2FP%3E%3CP%3EOther%20events%20have%20not%20times%20over%2060%20minutes%2C%20and%20the%20times%20show%20up%20as%2059%3A49%2C%20which%20is%2C%20best%20as%20I%20can%20tell%2C%20hh%3Amm%2C%20but%20could%20be%20mm%3Ass.%3C%2FP%3E%3CP%3EHow%20can%20I%20get%20the%205%20character%20time%20to%20match%20the%20hh%3Amm%3Ass%20AM%2C%20or%2C%20vise%20versa%3F%26nbsp%3B%20I've%20tried%20all%20the%20time%20formats%2C%20General%20format%2C%20and%20MOD...to%20no%20avail.%3C%2FP%3E%3CP%3EI%20have%20included%20a%20sample%20OneDrive%26nbsp%3B%3CA%20title%3D%22Excel%20Time%20Problem%22%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!ArlJqp1ZJxZNhHbGPROzfriAvNVu%3Fe%3Do0oQPh%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eeditable%20file%3C%2FA%3Eof%20the%20type%20I%20need%20to%20sort%20by%20time.%26nbsp%3B%20If%20it%20gets%20corrupted%2C%20it's%20replaceable%3B%26nbsp%3B%20Helpful%20formulas%20can%20be%20placed%20right%20in%20the%20file.%3C%2FP%3E%3CP%3EThanks%2C%20in%20advance....%20my%20first%20experience%20in%20the%20Excel%20community.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-814195%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814288%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20convert%2012%3A59%3A49%20AM%20(HH%3AMM%3ASS%20AM)%20to%2059%3A49%20(MM%3ASS)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395633%22%20target%3D%22_blank%22%3E%40mrbill47%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20check%20this%20formula%20in%20available%20in%20column%20E%20and%20F(RK.Solution1%20and%202).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(LEN(C3)%3D5%2CTIME(0%2CLEFT(C3%2C2)%2CRIGHT(C3%2C2))%2CC3)%26nbsp%3B%20-%20converts%205%20char%20in%20to%20MI%3ASS%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(LEN(C3)%26gt%3B5%2CTIME(MINUTE(C3)%2CSECOND(C3)%2C0)%2C%22%22)%20-%20converts%20MI%3ASS%20format%20to%20HH%3AMI%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20want%20check%20for%20more%20examples%20input.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20helps!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818289%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20convert%2012%3A59%3A49%20AM%20(HH%3AMM%3ASS%20AM)%20to%2059%3A49%20(MM%3ASS)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818289%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help.%26nbsp%3B%20I%20converted%20Col.%20E%2C%20and%20it%20worked%20on%20all%20the%20data%20in%20the%20column.%26nbsp%3B%20%26nbsp%3BI%20didn't%20mess%20with%20Col.%20F%2C%20as%20dividing%20Col.%20E%20by%204%20worked%20to%20get%20an%20accurate%20pace%20(that%20matched%20Col.%20F%2C%20only%20with%20more%20accuracy)..%3C%2FP%3E%3CP%3EI%20am%20ecstatic.%26nbsp%3B%20Would%20have%20taken%20me%20days%2C%20IF%20I%20ever%20got%20t%20right!%3C%2FP%3E%3CP%3EThanks%2C%20again%20for%20the%20help.%3C%2FP%3E%3CP%3EBill%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819376%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20convert%2012%3A59%3A49%20AM%20(HH%3AMM%3ASS%20AM)%20to%2059%3A49%20(MM%3ASS)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395633%22%20target%3D%22_blank%22%3E%40mrbill47%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBill%2C%3C%2FP%3E%3CP%3EGood%20to%20hear%20that%20it%20worked%20for%20you%20!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
mrbill47
New 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
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 !! 

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