Moving / shifting hours to minutes and minutes to seconds

AudioBabble · February 23, 2022, 10:05pm

Here’s an interesting quandry…

I’m pasting a music album track listing from a website into Calc.

The time values for each track are in MM:SS, but when pasted into Calc they get interpreted as HH:MM:SS - so for example 2:07 comes out as 02:07:00

Does anyone know of a formula or method by which I can “shift over” the values so that 02:07:00 becomes 00:02:07 ?

I’m sure it’s possible mathematically but it seems that side of my brain is a little disfunctional at the moment!

AudioBabble · February 23, 2022, 10:37pm

Actually the maths parts isn’t that hard… convert all to seconds, divide by 60, convert back to MM:SS

Now I just need to know how to actually do that in calc!

EarnestAl · February 23, 2022, 11:06pm

The problem is that Format Cell for hundredths of a second in Calc is not 02:07:00, but is 02:07.00 (decimal separator for the hundredths, not a colon).

Your workaround is simplest as long as days don’t get involved.

Paste your times in column D starting at D2.
Select column E, right-click and select Format Cell. In the dialogue box that opens, select tab Numbers, in the Category pane select Time, in the Format pane select the entry near the top with the Format Code [HH]:MM:SS.00 and OK.
Click in cell E2 and enter the formula =A2/60. Fill down

Result is 00:02:07.00

[EDIT] Copy the cells in column E and keeping the same selection click Edit > Paste special > Paste special > Values only then column D can be deleted

AudioBabble · February 24, 2022, 12:09pm

Thanks! I tried D2/60 formula first, but didn’t have the formatting right so result kept coming out a number rather than a time.

[HH]:MM:SS.00 works great - I just set it to have no decimal places and it comes out looking like HH:MM:SS

AudioBabble · February 23, 2022, 10:53pm

Cracked it… I used the following formula:

=((D186400)/60)/(2460*60)

Here, D1 is my incorrect time value of 02:07:00,

As long as the output cell is formatted as HH:MM:SS it gives me the correct value of 00:02:07

AudioBabble · February 23, 2022, 10:55pm

=((D1 * 86400)/60) / (24 * 60 * 60)

(added spaces just to make the formula a little clearer)

keme1 · February 24, 2022, 5:57am

That is exactly the same as @EarnestAl’s suggested formula, =D1/60. Your formula just multiplies that by 86400, then divides by (24*60*60) which is also 86400.

Also take note of the square brackets in the format. If your cell will ever contain a value in excess of 24 hours (e.g. when you sum your times), your format will discard the “days” (because your format does not have a placeholder for days) and fold hours back to 0. With [HH]:…. the value displayed will accumulate hours also beyond 24, instead of assuming days to be “part of the equation”.

The next challenge may be input. If you do manual input and have hours in the display format, hours need to be part of the input also. Formatting string [MM]:SS will act as an “input mask” and assume that you also start your input with minutes, s requested, but then your cell will not display hours.