Assuming data is in A2:E2, use these array* formulae:

F2: =SUM(IF(A2:E2<=8,A2:E2,8))

for sum of regular hours, and:

G2: =SUM(IF(A2:E28,A2:E2-8))

for sum of overtime hours.

* Array formula need to be committed using the key combination of Ctrl-
Shift-Enter (CSE) instead of the usual Enter. If you do this correctly
then Excel will wrap curly braces { } around the formula when viewed
in the formula bar - do not type these yourself. If you edit/amend the
formula you will need to use CSE again.

Hope this helps.

Pete


On Oct 20, 3:55*pm, Mike H wrote:
Hi,

For basic hours
=MIN(SUM(A2:E2),38)

For ot hours
=MAX(SUM(A2:E2)-38,0)

Mike

"Keep It Simple Stupid" wrote:



I need to keep my columns to a minimum. *

There are 5 columns for each employee (Monday-Friday). *Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon * * Tue * *Wed * *Thu * *Fri
8 * * * * *10 * * * 12 * * *6 * * * 10 * Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. *Any ideas?- Hide quoted text -

- Show quoted text -