Number stored as hh:mm:ss but I need it to be mm:ss

%3CLINGO-SUB%20id%3D%22lingo-sub-1438190%22%20slang%3D%22en-US%22%3ENumber%20stored%20as%20hh%3Amm%3Ass%20but%20I%20need%20it%20to%20be%20mm%3Ass%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1438190%22%20slang%3D%22en-US%22%3E%3CP%3EHello-%20I%20am%20currently%20cleaning%20legacy%20data.%20The%20data%20was%20stored%20as%20a%20Time%20(example%201%3A31%3A00%20AM)%20however%20I%20need%20it%20to%20reflect%201%20minute%20and%2031%20seconds.%20Is%20there%20any%20easy%20solution%20for%20this%3F%20I%20am%20currently%20delimiting%20based%20on%20colons%20then%20concatenating%20afterwards.%20which%20is%20super%20sick.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20much%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel%20problem.PNG%22%20style%3D%22width%3A%20613px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196241i8F08AD6128ADD503%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22excel%20problem.PNG%22%20alt%3D%22excel%20problem.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1438190%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1438309%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20stored%20as%20hh%3Amm%3Ass%20but%20I%20need%20it%20to%20be%20mm%3Ass%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1438309%22%20slang%3D%22en-US%22%3EThis%20might%20not%20do%20exactly%20what%20you%20need%20but%20it%20might%20be%20a%20stepping%20stone.%20What%20you%20can%20do%20is%20shift%20your%20hour%2Fminutes%20over%20to%20the%20minutes%2Fseconds%20column%20by%20using%20the%20excel%20%3DTIME()%20function%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20if%20Cell%20I1%20that%20you%20have%20selected%20in%20your%20screen%20is%201%3A31%3A00%20AM%2C%20then%20you%20do%20%3DTIME(0%2CHOUR(I1)%2CMINUTE(I1))%20and%20then%20format%20the%20cell%20using%20More%20Number%20Formats%2C%20go%20to%20Custom%2C%20and%20enter%20mm%3Ass%20as%20your%20format%20and%20it'll%20appear%20in%20your%20cell%20as%20just%2001%3A31.%20Note%20that%20doing%20it%20like%20this%20will%20make%20every%20time%20be%20considered%20%2212%3AMM%3ASS%20AM%22%20as%200%20in%20the%20hour%20position%20is%20read%20as%20midnight%2C%20but%20it'll%20still%20get%20you%20to%20where%20you%20need%20to%20be%20I%20believe.%3CBR%20%2F%3E%3CBR%20%2F%3ELet%20me%20know%20if%20you%20need%20more%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1438361%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20stored%20as%20hh%3Amm%3Ass%20but%20I%20need%20it%20to%20be%20mm%3Ass%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1438361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F688185%22%20target%3D%22_blank%22%3E%40mdeakins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EManual%20way%3A%3C%2FP%3E%0A%3CP%3E-%20Add%20number%2060%20to%20any%20cell%20somewhere%20in%20the%20workbook%3C%2FP%3E%0A%3CP%3E-%20Ctrl%2BC%20(copy)%20it%3C%2FP%3E%0A%3CP%3E-%20select%20your%20entire%20range%20with%20times%3C%2FP%3E%0A%3CP%3E-%20Paste-%26gt%3BPaste%20Special-%26gt%3BDivide-Ok%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20583px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196244i2C7F0BFF8FE1F200%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20right%20after%20that%20Ctrl%2B1%20and%20apply%20Time%20format%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorSergei%20Baklan_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3EHere%20we%20are%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20198px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196247iD62BF5F48D5E84E4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1438375%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20stored%20as%20hh%3Amm%3Ass%20but%20I%20need%20it%20to%20be%20mm%3Ass%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1438375%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F688185%22%20target%3D%22_blank%22%3E%40mdeakins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDon't%20know%20why%20but%20this%20screenshot%20is%20disappearing%20from%20previous%20post%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20471px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196249i4E9A24056F53A3F3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello- I am currently cleaning legacy data. The data was stored as a Time (example 1:31:00 AM) however I need it to reflect 1 minute and 31 seconds. Is there any easy solution for this? I am currently delimiting based on colons then concatenating afterwards. which is super sick. 

Any help is much appreciated

 

Thank you!

excel problem.PNG

 

3 Replies
Highlighted
This might not do exactly what you need but it might be a stepping stone. What you can do is shift your hour/minutes over to the minutes/seconds column by using the excel =TIME() function

So if Cell I1 that you have selected in your screen is 1:31:00 AM, then you do =TIME(0,HOUR(I1),MINUTE(I1)) and then format the cell using More Number Formats, go to Custom, and enter mm:ss as your format and it'll appear in your cell as just 01:31. Note that doing it like this will make every time be considered "12:MM:SS AM" as 0 in the hour position is read as midnight, but it'll still get you to where you need to be I believe.

Let me know if you need more help.
Highlighted

@mdeakins 

Manual way:

- Add number 60 to any cell somewhere in the workbook

- Ctrl+C (copy) it

- select your entire range with times

- Paste->Paste Special->Divide-Ok

image.png

- right after that Ctrl+1 and apply Time format

 

Here we are

image.png

Highlighted

@mdeakins 

Don't know why but this screenshot is disappearing from previous post

image.png