Argument

J

JAGC

I am new at creating formula in Excel. I am trying to create a specific
timesheet. The employee can work a maximum of 70 hours in 7 days. I have
created this formula, which works for the pass 6 days but when I use it for 7
days, I have too many argument. I also have to create a formula for 13 days
and 14 days (maximum of 120 hours).

Works for pass 6 days:
=IF(D25=0,"0",SUM(IF(D20=0,(D21:D25),IF(D21=0,(D22:D25),IF(D22=0,(D23:D25),IF(D23=0,(D24:D25),IF(D24=0,D25,IF(D20:D25>0,(D20:D25),""))))))))

Too many argument (7 days):
=IF(D26=0,"0",SUM(IF(D20=0,(D21:D26),IF(D21=0,(D22:D26),IF(D22=0,(D23:F26),IF(D23=0,(D24:D26),IF(D24=0,(D25:D26),IF(D25=0,D26,IF(D20:D26>0,(D20:D26),"")))))))))
 
B

Bob Phillips

How about

=SUM(D21:D25)

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

JAGC said:
I am new at creating formula in Excel. I am trying to create a specific
timesheet. The employee can work a maximum of 70 hours in 7 days. I have
created this formula, which works for the pass 6 days but when I use it for 7
days, I have too many argument. I also have to create a formula for 13 days
and 14 days (maximum of 120 hours).

Works for pass 6 days:
=IF(D25=0,"0",SUM(IF(D20=0,(D21:D25),IF(D21=0,(D22:D25),IF(D22=0,(D23:D25),I
F(D23=0,(D24:D25),IF(D24=0,D25,IF(D20:D25>0,(D20:D25),""))))))))

Too many argument (7 days):
=IF(D26=0,"0",SUM(IF(D20=0,(D21:D26),IF(D21=0,(D22:D26),IF(D22=0,(D23:F26),I
F(D23=0,(D24:D26),IF(D24=0,(D25:D26),IF(D25=0,D26,IF(D20:D26>0,(D20:D26),"")
))))))))
 
B

Bob Phillips

Sorry, meant

=SUM(D21:D26)

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

JAGC said:
I am new at creating formula in Excel. I am trying to create a specific
timesheet. The employee can work a maximum of 70 hours in 7 days. I have
created this formula, which works for the pass 6 days but when I use it for 7
days, I have too many argument. I also have to create a formula for 13 days
and 14 days (maximum of 120 hours).

Works for pass 6 days:
=IF(D25=0,"0",SUM(IF(D20=0,(D21:D25),IF(D21=0,(D22:D25),IF(D22=0,(D23:D25),I
F(D23=0,(D24:D25),IF(D24=0,D25,IF(D20:D25>0,(D20:D25),""))))))))

Too many argument (7 days):
=IF(D26=0,"0",SUM(IF(D20=0,(D21:D26),IF(D21=0,(D22:D26),IF(D22=0,(D23:F26),I
F(D23=0,(D24:D26),IF(D24=0,(D25:D26),IF(D25=0,D26,IF(D20:D26>0,(D20:D26),"")
))))))))
 
J

JAGC

Thanks Bob.
But I am sorry I omitted a crucial part, after the employee takes a day off
the clock restart and the employee can work another 70 hours. The day off
can happen at any time during the month.
 
B

Bob Phillips

In E21, add

=IF(D21=0,0,SUM(INDEX($D$21:D21,MAX(1,MAX(IF($D$21:D21=0,ROW($D$21:D21)))-MI
N(ROW($D$21:D21))+1)):D21))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Copy this formula down.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run macro on selected sheets 10
Clear Contents Help 3
If condition for Blank Cell 4
VBA Help 3
Save as 13
Is there a better way? 10
Macro maximum range lengths 2
simple Question 1

Top