Sum of Daily Overtime

  • Thread starter Thread starter Keep It Simple Stupid
  • Start date Start date
K

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?
 
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
 
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
 
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.
 
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
 
Forgot about the CSE. Darn it! This is an incredible help! Thanks a bunch
everyone!
 
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
 
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.
 
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!
 
Back
Top