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,(D2125),IF(D21=0,(D2225),IF(D22=0,(D2325),IF(D23=0,(D2425),IF(D24=0,D25,IF(D2025>0,(D2025),""))))))))

Too many argument (7 days):
=IF(D26=0,"0",SUM(IF(D20=0,(D2126),IF(D21=0,(D2226),IF(D22=0,(D23:F26),IF(D23=0,(D2426),IF(D24=0,(D2526),IF(D25=0,D26,IF(D2026>0,(D2026),"")))))))))

How about

=SUM(D2125)

--
HTH

Bob Phillips

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

"JAGC" <(E-Mail Removed)> wrote in message
news:CD95489D-A53A-4151-B13E-(E-Mail Removed)...
> 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,(D2125),IF(D21=0,(D2225),IF(D22=0,(D2325),I
F(D23=0,(D2425),IF(D24=0,D25,IF(D2025>0,(D2025),""))))))))
>
> Too many argument (7 days):
>
=IF(D26=0,"0",SUM(IF(D20=0,(D2126),IF(D21=0,(D2226),IF(D22=0,(D23:F26),I
F(D23=0,(D2426),IF(D24=0,(D2526),IF(D25=0,D26,IF(D2026>0,(D2026),"")
))))))))

Sorry, meant

=SUM(D2126)

--
HTH

Bob Phillips

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

"JAGC" <(E-Mail Removed)> wrote in message
news:CD95489D-A53A-4151-B13E-(E-Mail Removed)...
> 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,(D2125),IF(D21=0,(D2225),IF(D22=0,(D2325),I
F(D23=0,(D2425),IF(D24=0,D25,IF(D2025>0,(D2025),""))))))))
>
> Too many argument (7 days):
>
=IF(D26=0,"0",SUM(IF(D20=0,(D2126),IF(D21=0,(D2226),IF(D22=0,(D23:F26),I
F(D23=0,(D2426),IF(D24=0,(D2526),IF(D25=0,D26,IF(D2026>0,(D2026),"")
))))))))

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.

"Bob Phillips" wrote:

> Sorry, meant
>
> =SUM(D2126)
>
> --
> HTH
>
> Bob Phillips
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "JAGC" <(E-Mail Removed)> wrote in message
> news:CD95489D-A53A-4151-B13E-(E-Mail Removed)...
> > 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,(D2125),IF(D21=0,(D2225),IF(D22=0,(D2325),I
> F(D23=0,(D2425),IF(D24=0,D25,IF(D2025>0,(D2025),""))))))))
> >
> > Too many argument (7 days):
> >
> =IF(D26=0,"0",SUM(IF(D20=0,(D2126),IF(D21=0,(D2226),IF(D22=0,(D23:F26),I
> F(D23=0,(D2426),IF(D24=0,(D2526),IF(D25=0,D26,IF(D2026>0,(D2026),"")
> ))))))))
>
>
>

In E21, add

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

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)

"JAGC" <(E-Mail Removed)> wrote in message
news2A86965-DD4E-4F36-B8E3-(E-Mail Removed)...
> I thought that a section of the form may help.
>
> A B C D
> E
> 40 15-Jan-07 08:00:00 22:00:00 14.00 42.00
> 41 16-Jan-07 08:00:00 20:00:00 12.00 54.00
> 42 17-Jan-07 08:00:00 22:00:00 14.00 68.00
> 43 18-Jan-07 0.00 0
> 44 19-Jan-07 08:00:00 22:00:00 14.00 14.00
> 45 20-Jan-07 08:00:00 22:00:00 14.00 28.00
>
>
> "JAGC" wrote:
>
> > 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.
> >
> > "Bob Phillips" wrote:
> >
> > > Sorry, meant
> > >
> > > =SUM(D2126)
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (there's no email, no snail mail, but somewhere should be gmail in my
addy)
> > >
> > > "JAGC" <(E-Mail Removed)> wrote in message
> > > news:CD95489D-A53A-4151-B13E-(E-Mail Removed)...
> > > > 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,(D2125),IF(D21=0,(D2225),IF(D22=0,(D2325),I
> > > F(D23=0,(D2425),IF(D24=0,D25,IF(D2025>0,(D2025),""))))))))
> > > >
> > > > Too many argument (7 days):
> > > >
> > >
=IF(D26=0,"0",SUM(IF(D20=0,(D2126),IF(D21=0,(D2226),IF(D22=0,(D23:F26),I
> > >
F(D23=0,(D2426),IF(D24=0,(D2526),IF(D25=0,D26,IF(D2026>0,(D2026),"")
> > > ))))))))
> > >
> > >
> > >