If Formula

[susieh]

Can someone please help me to create a formula to do the following:

I am creating a timecard and if there are more than 40 hours worked in the
week, then it is overtime. There are either 2 or 3 weeks per time card. So
for example, for work week 1 the total hours worked is 42 (cell F10), the
total for work week 2 is 30 hours ( cell N10), and the total for work week 3
is 41 (cell V10). I want the total of the hours less than 40 to go into cell
O15 and the total of the hours over 40 to go into cell O16. So in the above
scenario, O16 would be 3 and O15 would be 110. If there is no overtime, I
want "0" to be put into cell O16. If there are only 2 weeks in the pay
period, I want the formula to calculate only the total of the 2 weeks that
are filled in on the time card.

Is this possible?

 

[Marcelo]

An ease way, if is necessary include the 4th week,


O15 = SUM(F10,N10,V10)-O16
O16 = IF(F10>40,F10-40,0)+IF(N10>40,N10-40,0)+IF(V10>40,V10-40,0)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"susieh" escreveu:

 

[susieh]

Thank you, Marcelo!!! We have been trying for weeks to figure that out. You
were a HUGE help.