Please, help! Mathematical operations on data types DURATION

For over a month now, I have not been able to achieve the correct multiplication of time by a number. All found solutions, including this one, do not work correctly. Please tell me a way to multiply the DURATION by a number. Why aren’t the developers adding this feature? This has been working in Google Sheets for a long time.
I have the duration of the operations, for example: “00:03:54” or “00:00:47” or “01:43:22”. And I need to multiply this duration by the number of repetitions of the operation to get the total duration. For example, “00:00:46 * 24 = 00:18:24”.

  1. Convert duration to total number of seconds.
  2. Multiple #1 result by your multiplier.
  3. Convert #2 result back to duration type.

1,2:

TOTALSECONDS([duration]) * X

3:

RIGHT( "00" & FLOOR( [#2] / 60.0 / 60.0 ) , 3 )
& ":" &
RIGHT( "0" & MOD( [#2] / 60.0 , 60.0 ) , 2 )
& ":" &
RIGHT( "0" & MOD( [#2] , 60 ) , 2 )

Which seems to be almost an identical post (except for the use of TOTALSECONDS(), which I think is newer), to what Steve wrote in the thread you linked.

Probably because very few people would need such a feature, and it is easy to accomplish otherwise.

How so?

3 Likes

I’ve tried it all before. Using your version of this option, I get incorrect results:
“00:00:47 * 99 = 1:67:13” or “00:00:47 * 97 = 1:71:40” or “00:01:08 * 1 = 0:33:08”



Seems to work for me.

2 Likes

I, too, during the test, everything is fine, but an incorrect value is written in the field.

Again, seems to work fine for me (with a x2 factor):
image
image

Perhaps try to ensure that your entire spreadsheet is formatted as Plain Text. Maybe the spreadsheet is trying to auto-convert/auto-format the value a certain way, like as a date or something?

3 Likes

After some agony, I came to the following solution. I have replaced the minutes definition line in your formula.
Before:

RIGHT( "0" & MOD( [#2] / 60.0 , 60.0 ) , 2 )

After:

RIGHT( "0" & FLOOR(MOD( [#2] / 60.0 , 60.0 )) , 2 )

It turned out that your original formula, for some reason unknown to me, counted the minutes incorrectly and cut off the symbols. Now everything works for me correctly. Thank you very much for your participation!

1 Like

Hmm.

The reason I use the RIGHT("0" & ...) is to make sure that any single digit minutes will be displayed with a preceding zero. I’m not actually sure if it is required to build the Duration string correctly or not, it’s more of a “better safe than sorry” situation.

Assume the amount of minutes is “5.6”, then RIGHT( "0" & "5.6" , 2) would return “.6”, which would absolutely mess it up. However, MOD() is supposed to return an Integer/Number value, not a Decimal.

So I can’t explain what happened there…

Yes, I was just getting something like “.6”