Multiply Duration by a number

Hi!

I would like to multiply the duration (hours) by a number ( by employee), but when I do the expression, there is always a message saying I canโ€™t multiply a number by a duration.

I would like to keep the Duration writing. Exemply 6:30:00 * 3 = 19:30:00

Somebody have a solution?

Thanks!

Solved Solved
0 5 1,564
1 ACCEPTED SOLUTION

LeventK
Participant V

@Plantenance_Landscap
I believe this might help in the AppFormula of your result/calculation column. The result type is: Duration

CONCATENATE(
	HOUR([Duration]-"000:00:00") * [EmployeeNumber] + FLOOR((MINUTE([Duration]-"000:00:00") * [EmployeeNumber] - MOD(MINUTE([Duration]-"000:00:00") * [EmployeeNumber],60))/60) + FLOOR((SECOND([Duration]-"000:00:00") * [EmployeeNumber] - MOD(SECOND([Duration]-"000:00:00") * [EmployeeNumber],3600))/3600),
	":",
	RIGHT("0"&MOD(MINUTE([Duration]-"000:00:00") * [EmployeeNumber] + FLOOR((SECOND([Duration]-"000:00:00")*[EmployeeNumber] - MOD(SECOND([Duration]-"000:00:00") * [EmployeeNumber],60))/60),60),2),
	":",
	RIGHT("0"&MOD(SECOND([Duration]-"000:00:00") * [EmployeeNumber],60),2)
)

View solution in original post

5 REPLIES 5

Steve
Participant V

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)
)

LeventK
Participant V

@Plantenance_Landscap
I believe this might help in the AppFormula of your result/calculation column. The result type is: Duration

CONCATENATE(
	HOUR([Duration]-"000:00:00") * [EmployeeNumber] + FLOOR((MINUTE([Duration]-"000:00:00") * [EmployeeNumber] - MOD(MINUTE([Duration]-"000:00:00") * [EmployeeNumber],60))/60) + FLOOR((SECOND([Duration]-"000:00:00") * [EmployeeNumber] - MOD(SECOND([Duration]-"000:00:00") * [EmployeeNumber],3600))/3600),
	":",
	RIGHT("0"&MOD(MINUTE([Duration]-"000:00:00") * [EmployeeNumber] + FLOOR((SECOND([Duration]-"000:00:00")*[EmployeeNumber] - MOD(SECOND([Duration]-"000:00:00") * [EmployeeNumber],60))/60),60),2),
	":",
	RIGHT("0"&MOD(SECOND([Duration]-"000:00:00") * [EmployeeNumber],60),2)
)

Its working perfectly! Thank you so much!!

Youโ€™re welcome

Unfortunately, your formula doesnโ€™t always work correctly. For example, multiply the duration โ€œ000: 19: 19โ€ by the number 22, you get 6h 04min instead of 7h 04min.
p.s.: Sorry for bad English, I work through google translator

Top Labels in this Space