|
Argument
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:D250,(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:D260,(D20:D26)," "))))))))) |
Argument
How about
=SUM(D21:D25) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "JAGC" wrote in message ... 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:D250,(D20:D 25),"")))))))) 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:D260,(D20:D26),"") )))))))) |
Argument
Sorry, meant
=SUM(D21:D26) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "JAGC" wrote in message ... 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:D250,(D20:D 25),"")))))))) 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:D260,(D20:D26),"") )))))))) |
Argument
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(D21:D26) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "JAGC" wrote in message ... 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:D250,(D20:D 25),"")))))))) 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:D260,(D20:D26),"") )))))))) |
Argument
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) "JAGC" wrote in message ... 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(D21:D26) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "JAGC" wrote in message ... 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:D250,(D20:D 25),"")))))))) 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:D260,(D20:D26),"") )))))))) |
| All times are GMT +1. The time now is 04:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com