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,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?
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.
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):
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?
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!
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โ
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |