Help

Multiplication formula

Topic Labels: Formulas
920 1
cancel
Showing results for 
Search instead for 
Did you mean: 
zodeci
4 - Data Explorer
4 - Data Explorer

I am trying to calculate the sum of man hours for two teams that have different production hours and different number of staff members.

This works for one team member of {Staff (Team 1)} but I can’t seem to get it to multiply by {Staff (Team 1)}, let alone sum {Staff (Team 2} with same formula.

DATETIME_DIFF({End (Team 1)},{Start (Team 1)},‘minutes’) / 60-{Breaks in H (Team 1)}

My attempt to achieve this that obviously don’t work lol:

DATETIME_DIFF({End (Team 1)},{Start (Team 1)}*{Staff (Team 1)},‘minutes’) + DATETIME_DIFF({{End (Team 2)}, {Start (Team 2)})/ 60-{Breaks in H (Team 1)}-{Breaks in H (Team 2)}*sum({Staff (Team 1){{Staff (Team 2)})

Thanks for your help in advance

1 Reply 1

It looks like you tried to do, among other things, “How many minutes apart is Date 2 from (Date 1 * number of people)?” which doesn’t make any sense. The proper way to do that would be “How many minutes apart is Date 2 from Date 1? Multiply that time difference by a number of people.” Your other issues include improper syntax (i.e. {{End (Team 2)} instead of {End (Team 2)}, not separating the arguments in your SUM() function with a comma, etc.).

SUM(
   (DATETIME_DIFF({End (Team 1)}, {Start (Team 1)}, 'minutes') * {Staff (Team 1)}),
   (DATETIME_DIFF({End (Team 2)}, {Start (Team 2)}, 'minutes') * {Staff (Team 2)})
)/ 60 - (
   SUM({Breaks in H (Team 1)}, {Breaks in H (Team 2)}) *
   SUM({Staff (Team 1)}, {Staff (Team 2)})
)

^ In plain English, that formula reads:
(The total number of hours worked by the Staff of Team 1 and Team 2) - (The total number of break hours spend by the Staff of Team 1 and Team 2)