Subtracting lunch break from hours worked

Question

I'm trying to calculate the total hours worked, not including lunch breaks. I'm using standard numbers for hours worked such as 8, 10, 12 etc. However, it needs to reflect a 30 minute break for any shift that is less than 12 hours and a 1 hour break for any shift that is 12 hours or more. How would I go about doing this?

Answer 1

If the hours are in range A2:A8, then the following formulas will work:

SUMPRODUCT

=SUM(A2:A8, -SUMPRODUCT(
   A2:A8<>"", IF(A2:A8<12,0.5,1))

Or

VLOOKUP

=SUM(A2:A8, -ARRAYFORMULA(
   VLOOKUP(TOCOL(A2:A8,1),
     {0,0.5;12,1},2)))

LET

If you like, the LET function can be included in either formula:

=LET(rng,A2:A8, 
   SUM(rng, -SUMPRODUCT(
     rng<>"", IF(rng<12,0.5,1)) 

Or

=LET(rng,A2:A8, 
   SUM(rng, -ARRAYFORMULA(
     VLOOKUP(TOCOL(rng,1), 
       {0,0.5;12,1},2)))