get the adherence based on category

[stoey]

hi,

im not sure if this is the proper thread to where this should be placed but i'll try my luck here...im creating a break adherence spread sheet but i need formulas to fill in...i messed up that's why i need your help guys...here's the thing:

if i have 3 parts break then how would the formula be then? will it stays as like this? since break is divided into Break1: 30 minutes (0.5), Break2: 15 minutes (0.25) and Break3: 30 minutes (0.5)...then formula would be like: A=8.5+0.5 / 9.75 for break1, then A=8.5+0.25 / 9.75 for break2, then A=8.5+0.5 / 9.75 for break3? well tried this formula with the following values:
break 1
IN:11:30 AM OUT: 12:00PM DIFFERENCE:0:30 or 0.5
break 2
IN:1:45 PM OUT: 2:00PM DIFFERENCE:0:30 or 0.25
break 3
IN:4:00 PM OUT: 4:35PM DIFFERENCE:0:35 or 0.58

TOTAL DIFFERENCE IS 1.33

then using the formula A=8.5+1.33/9.75 gives me 100.8547009 percent...this should not exceed 100% it should however decrease since the total paid break is only 1.25 and in excess of this paid break should be a deduction to the total 100% score...sorry for being a pain but i hope you can help me with this....i appreciate your efforts..please feel free to correct the formula as i am at a lost

regards,

stoey

[barryleajo]

Hi stoey
this sort of approach may help you organise your thoughts and help develop your formulae
see attached