Excel formula

[Vbel]

I need help with a formula in Excel. It is a timesheet that totals the
hours. An employee is allowed to work a max of 7 days or 70 hours, in
which case it sets back to 0, otherwise total. I am trying to use a
nested IF formula but can't get all the arguments to work with each
other:

Sample data
A B
1 10 10
2 10 20
3 10 30
4 10 40
5 10 50
6 10 60
7 10 70

=IF B7<>70,SUM(A1:A7),"0"
IF A1=0, SUM(A2:A7)
IF A2=0, SUM(A3:A7)
IF A3=0, SUM(A4:A7)
IF A4=0, SUM(A5:A7)
IF A5=0, SUM(A6:A7)
IF A6=0, A7

Any help would be appreciated.

 

[Harlan Grove]

Vbel wrote...

I need help with a formula in Excel. It is a timesheet that totals the
hours. An employee is allowed to work a max of 7 days or 70 hours, in
which case it sets back to 0, otherwise total. I am trying to use a
nested IF formula but can't get all the arguments to work with each
other:

....

Unclear. Do you fire these employees after 7 days or 70 hours? Are you
seeking a means of resetting to zero after 7-day periods? What happens
if employees work more than 70 hours in 7 days? Unless they're rigidly
controlled in specific locations, there's a chance they could exceed
arbitrary caps.

=IF B7<>70,SUM(A1:A7),"0"

This is where B7 could be greater than 70. And DON'T mix numbers and
text, i.e., DON'T return "0" rather than 0. You'd be begging for even
more problems using "0".

I suspect you should be using

=MIN(70,SUM(A1:A7))

which is a complete formula.

IF A1=0, SUM(A2:A7)
IF A2=0, SUM(A3:A7)
IF A3=0, SUM(A4:A7)
IF A4=0, SUM(A5:A7)
IF A5=0, SUM(A6:A7)
IF A6=0, A7

All unnecessary. If A1 = 0, then SUM(A1:A7) = A1+SUM(A2:A7) =
0+SUM(A2:A7) = SUM(A2:A7), and similarly for A2, A3, . . ., A7.

 

[Sandy Mann]

I'm not sure that I understand what it is you want but would:

=IF(SUM($A$1:A1)>=70,0,A1)

entered in A1 and copied down do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Vbel]

Sorry about the unclear info. When the hours reach 70 it sets it back
to zero, OR if they reach 7 days it sets back to zero. An employees is
not allowed to work over 70 hrs or 7 days (whichever comes 1st) without
taking a day off.

 

[Harlan Grove]

Vbel wrote...

Sorry about the unclear info. When the hours reach 70 it sets it back
to zero, OR if they reach 7 days it sets back to zero. An employees is
not allowed to work over 70 hrs or 7 days (whichever comes 1st) without
taking a day off.

If personnel policies alone would prevent any employee working over 70
hours in a 7 day period, your time sheet data wouldn't ever have any
instances of employees working more than 70 hours in any 7 day period,
so you don't need to handle that in your formulas. If so, all you need
to do is reset to 0 after every 7 day period.

If time worked for each day were in B2:B22, and cumulative time worked
in the current 7 day period were in C2:C22, try these formulas.

C2:
=B2

C3:
=B3+(MOD(COUNT(A$3:A3),7)>0)*C2

Fill C3 down into C4:C22.

I'll end by saying personnel policies are never perfect. If any subject
employee could be in the course and scope of their job while in transit
on public roads, they *could* work (as far as the government is
concerned) more than 70 hours in a 7 day period if they travel anywhere
for work reasons having worked more than 168 - 24 * days remaining in 7
day period. It wouldn't hurt to be able to handle total work hours over
70 hours just in case. Otherwise you're setting yourself up to be a
classic example of Murphy's Law.

 

[Vbel]

Thank you! I tweaked the formula a bit and it works great.

C3:
=IF(B3=0,0,(IF(C2=70,0,(B3+(MOD(COUNT(A$2:A3),7)>0)+C2))))