Please, help! Mathematical operations on data type...

Ruslan_Akiev · 07-22-2021 10:14 AM

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”.

Marc_Dillon

Convert duration to total number of seconds.
Multiple #1 result by your multiplier.
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.

Multiply Duration by a number

It’s a three-part process: 1) convert the original duration to a number of seconds; 2) multiply the number of seconds; 3) convert the number of seconds to a duration. You’ll need to add a column to perform (1) and (2), and another to perform (3). The app formula for the first column (type Number 😞
(
  (
    (HOUR([Duration]) * (60 * 60))
    + (MINUTE([Duration]) * 60)
    + SECOND([Duration])
  )
  * [Multiplier]
)
And for the second column (type Duration 😞
(
  ([Seconds] / (60 * 60))
  & ":"
  & ([Seconds] / 60)
  & ":"
  & MOD([Seconds], 60)
)

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

How so?

Ruslan_Akiev

Marc_Dillon:

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 )

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”

Marc_Dillon

Seems to work for me.

Ruslan_Akiev

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

Marc_Dillon

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

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?

Ruslan_Akiev

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!

Marc_Dillon

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…

Ruslan_Akiev

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

Please, help! Mathematical operations on data types DURATION