need help with formula

  • Thread starter Thread starter bridgeb3
  • Start date Start date
B

bridgeb3

We have a spreadsheet that shows each day worked and OT worked as the
columns (14 Columns, Sun-Sat + OT) and employees as the rows. We have
a Reg Total (P) that will show regular hours worked up to 40 and OT
hours worked (Q) that will show any hours worked over 40.

Example, if employee A worked 45 hours during the week (added up from
B-0) then I want P to only show 40 and Q to show 5. Also if employee
B only works 37 hours, I want P to show 37 and Q to show zero.

What formulas can I use in these two cells to get the info that I
need?
 
Well, first of all, working 7 days a week, over a two week period, should
give more than 80 hours, never mind more than 40?

Do you want to calculate per fortnight, or do you want to calculate per week?
If the first, then use (in P) something like
=IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)>40,40,SUM(B2:O2)))
Just make sure about the 40/80 story.

In Q2, enter the following formula
=IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)>40,SUM(B2:O2)-40,0))

I take it you have quantities in B:O, formatted as General, or as Number
 
Well, first of all, working 7 days a week, over a two week period, should
give more than 80 hours, never mind more than 40?

Do you want to calculate per fortnight, or do you want to calculate per week?
If the first, then use (in P) something like
=IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)>40,40,SUM(B2:O2)))
Just make sure about the 40/80 story.

In Q2, enter the following formula
=IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)>40,SUM(B2:O2)-40,0))

I take it you have quantities in B:O, formatted as General, or as Number

--
Hth

Kassie Kasselman
Change xxx to hotmail







- Show quoted text -
Click to expand...

Thank you! That worked like a charm. It is actually only one week,
but there were 14 columns because we had one column for reg hours and
one column for OT hours for each day of the week.
 
Back
Top