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

[mdeakins]

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!

[#2]

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.

[#3]

@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

- right after that Ctrl+1 and apply Time format

Here we are

[#4]

@mdeakins 

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