Sum of Daily Overtime

[Keep It Simple Stupid]

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?

 

[Mike H]

Hi,

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

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


Mike

 

[JP]

Assuming your headers (Mon, Tue, etc) were in row 1, and the number of
hours in row 2, with everything starting in A1, this formula would do
what you want.

=IF(SUM(A2:E2)<=40,SUM(A2:E2)&" Hours","40 regular hrs,
"&(SUM(A2:E2)-40)& " Overtime Hrs")


--JP

 

[Pete_UK]

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:E2>8,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

 

[Keep It Simple Stupid]

I'm not sure this is what I am looking for. I need the OT to be calculated
daily instead of weekly. The OT is based on 8 hour days. In my example, the
total should be 38 regular hours and 8 OT hours - whereas if it was based on
a weekly 40 hours it would have been 40 regular hours, 6 OT hours.

Basically, I need a way to sum everything over 8 hours, subtracting 8 hours
from each instance.

 

[Peo Sjoblom]

To get the total OT

=SUM(IF(A1:A5>8,A1:A5-8))


To get the total regular time



=SUM(A1:A5)-SUM(IF(A1:A5>8,A1:A5-8))


both entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom

 

[Keep It Simple Stupid]

Forgot about the CSE. Darn it! This is an incredible help! Thanks a bunch
everyone!

 

[Teethless mama]

For regular hours:
=SUM(A1:E1)-(SUMIF(A1:E1,">8")-(COUNTIF(A1:E1,">8")*8))

For overtime hours:
=SUMIF(A1:E1,">8")-(COUNTIF(A1:E1,">8")*8)

Just press ENTER

 

[ShaneDevenshire]

Hi,

As said for the total overtime in F1 =SUM(IF(A1:E1>8,A1:E1-8,0))
but for the total regular time in G1 =SUM(A1:E1)-F1

Adjust your ranges as necessary.

 

[Pete_UK]

You're welcome - glad to help.

Pete

Forgot about the CSE.  Darn it!  This is an incredible help!  Thanks a bunch
everyone!











- Show quoted text -

 

[Keep It Simple Stupid]

This one worked the best because the other ones were counting my 0's as
negative numbers, etc. (For overtime hours:
=SUMIF(A1:E1,">8")-(COUNTIF(A1:E1,">8")*8)
Thanks so much!