Formula to calculate regular hours and overtime hours

**judojames** · 07-30-2014, 03:53 PM

I could use some help coming up with a formula for calculating reg and ot hours for multiple jobs.

Example

A1-job#1
A2-job#2
B1-5
B2-5
C1-5
C2-5
D1-5
D2-5
E1-5
E2-5
F1-5
F2-5
G1 needs to = 20
G2 needs to = 20
H1 needs to be 5
H2 needs to be 5

Any help would be appreciated.
Thanks in advanced

**Paul** · 07-30-2014, 04:10 PM

In G1 use:

=MIN(20,SUM(B1:F1))

In H1 use:
=SUM(B1:F1)-G1

Copy those to G2:H2.

**judojames** · 07-30-2014, 05:25 PM

Paul-Those formulas work, but the min value isn't always going to be 20 for either row.

**Paul** · 07-30-2014, 05:30 PM

Well, that proves that neither Excel or I are mind-readers. If you want a solution that meets your requirements, you're going to have to provide those requirements. Sample data beyond one example wouldn't hurt either.