formulas & formats the same, why are calculation different?

Question

Week 7 IN OUT IN OUT Regular OT Notes

Monday 2/12/2024 6:15 AM 4:35 PM 8.00 2.33

Tuesday 2/13/2024 6:20 AM 5:00 PM 8.00 2.67

Wed 2/14/2024 6:30 AM 7:35 PM 8.00 5.08

Thursday 2/15/2024 6:00 AM 4:45 PM 8.00 2.75

Friday 2/16/2024 6:05 AM 6:35 PM 8.00 4.50

								                                             Subtotals Week 7			40.00	17.33 

												 

												 

Week 8 IN OUT IN OUT Regular OT Notes

Monday 2/19/2024 8:00 AM 3:00 PM 0.29 0.00

Tuesday 2/20/2024 8:00 AM 3:00 PM 0.29 0.00

Wed 2/21/2024 8:00 AM 3:00 PM 0.29 0.00

Thursday 2/22/2024 8:00 AM 3:00 PM 0.29 0.00

Friday 2/23/2024 8:00 AM 3:00 PM 0.29 0.00

								                                             Subtotals Week 8			 1.46	0.00 

Regular time formula =IF(SUM(((D74-C74)+(F74-E74))*24)>=8,8,SUM(((D74-C74)+(F74-E74)*24)))

OT formula =MAX(0,SUM(((D74-C74)+(F74-E74))*24)-8)

Answer 1

I would use MMULT

Answer 2

Regular time formula =IF(SUM(((D74-C74)+(F74-E74))*24)>=8,8,SUM(((D74-C74)+(F74-E74)*24)))

OT formula =MAX(0,SUM(((D74-C74)+(F74-E74))*24)-8)

You have a misplaced right-parenthesis in the second expression. Compare with the first expression.

That might be a lot easier to see and avoid if you eliminate the befuddling redundant parentheses.

Also, the use of SUM is redundant and serves no useful purpose in this context.

And finally, you can use MIN in the regular-time formula.

The formulas become:

Regular time formula in G4: =MIN(8, (D74-C74+F74-E74)*24)
OT formula in H4: =MAX(0, (D74-C74+F74-E74)*24 - 8)

Note: Apparently, column C contains dates that are formatted as Custom dddd .

Answer 3

Did you forget to multiply the difference with 24 in week 8?