Frank Kabel, please help with last formula

[sonar]

Hi

I got the formula to take and it does not show the usual error anymore
but I have the problem that its not calculating the cells. Because
dont understand the formula, or how it works, is there something I di
not do that I was supposed to.

'email_me' ([email protected])

Attachment filename: sonar-file.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52566

 

[Frank Kabel]

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)))

 

[sonar]

Ok, it is calculating now, but just to be sure, what format should the
cell be in, General or time?

 

[Frank Kabel]

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

 

[sonar]

does that mean that it will never display more than 24hrs

 

[Frank Kabel]

Hi
if you use the custom format [hh]:mm it WILL display more
than 24 hours

 

[sonar]

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

 

[Frank Kabel]

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)))

 

[sonar]

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

 

[Frank Kabel]

Hi
and what is your expected result for these cases (if he has less than 5
entries)?

 

[sonar]

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