Frank Kabel, please help with last formula

  • Thread starter Thread starter sonar
  • Start date Start date
Hi
in your case use the following array formula:
=SUM(N(OFFSET($A$9,0,SMALL(IF(ISNUMBER(L9:BH9)*(MOD(COLUMN
(L9:BH9)-12,8)=0),COLUMN(L9:BH9)),{1;2;3;4;5})-1,1,1)))
 
Ok, it is calculating now, but just to be sure, what format should the
cell be in, General or time?
 
Hi
sorry I forgot to mention this. format this cell with the
custom format
[hh]:mm

the [] brackets will prevent the rollover after 24 hours
 
Ok, I see what it does. It is only seeing the first 5, and ignoring th
rest. It is not taking the first 5 out of the 7 that has hours though
So, if the first 3 does not have hours in, it will only see the 2 ou
of the 5 that has and ignore the rest.

I needed it to see the first 5 with hours in eg. if the hours wa
captured on tue, wed, thu, fri, sat,son. it should see tue, wed, thu
fri, sat and ignore son. If hours was captured on monday, tue, wed
thu, fri, sat, son. It should see the first 5 - mon, tue, wed, thu
fri and ignore the sat and sonday. etc

a person can work any 5days in the week, not necessary from mon - fri.
it can be wed - son or tue - sat. and any day thereafter is overtime.

can this formula be modified to do that
 
Hi
the formula should take only cells with a value in it. If you want to
check for hours >0 try
=SUM(N(OFFSET($A$9,0,SMALL(IF((L9:BH9>0)*(MOD(COLUMN
(L9:BH9)-12,8)=0),COLUMN(L9:BH9)),{1;2;3;4;5})-1,1,1)))
 
I find the second one to work nicely, but what could I do in a cas
where an employee does not pitch up for one ore more of his/her shifts
I tried having less shifts and it gave me the #NUM error. Can that b
fixed
 
Well, I know that it cant exceed 5 days, but I also need to mak
provision in case its less than 5 days, still to calculate, but not t
freak out if it is less than 5 days
 
Back
Top