# Measuring the time an employee deviates from a prescribed schedule

#### Brian_

##### New Member
My company staffs its call center with very strict adherence to schedule rules and each employee's adherence rate is tied to advancement, evaluations and promotions. There are times, however, when exceptions are made and because of this, many employees will submit a request for every deviation in an attempt to "fix" their adherence.

Most of the rules are related to the length of the deviation. 15 minutes is ok to adjust, 14 minutes or less is not allowed. There is a caveat that if mutiple deviations with the same reason code accumulate to 30 minutes on any given day, an adjustment is allowed.

I have formulas to cover the 15 minute rules but the cumulative 30 minute formula escapes me.

In Column A will be the employee name, Column B will be the date and Column I will be the length of deviation.

I'm trying to write a formula that will find all the entries on a given date for a given employee, then check for deviations <=14 and sum them. If they add up to >=30 minutes, then return "Compliant".

I hope I've made this as clear as possible and I appreciate any help anyone can provide.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### alx7000

##### Board Regular
Hi Brian,

Welcome to the board! If I understand correctly the following should work (assumes data is in rows 2-29):
Excel Workbook
K
2COMPLIANT
Sheet1
Excel 2010
Cell Formulas
RangeFormula
K2=IF(SUMPRODUCT((\$A\$2:\$A\$29=A2)*(\$B\$2:\$B\$29=B2)*(\$I\$2:\$I\$29<15)*(\$I\$2:\$I\$29))>=30,"COMPLIANT","NON-COMPLIANT")

The (\$A\$2:\$A\$29=A2) returns TRUE where the employee matches
The (\$B\$2:\$B\$29=B2) returns TRUE where the date matches
The (\$I\$2:\$I\$29<15) returns TRUE where the deviation is less than 15 mins

Multiplying the above three conditions gives an array with 1's where all three conditions are met, 0's otherwise. This is multiplied by (\$I\$2:\$I\$29) to give the deviations for these.

Sumproduct is then used to sum this array (avoids using a CSE formula) and an IF function in wrapped around it to return COMPLIANT is the sum is 30 or greater.

Hope this is clear and does what you require, any questions please let me know.

Cheers,
alx7000

#### Brian_

##### New Member
That worked brilliantly! Thanks so much alx7000! And thanks also for the warm welcome to the community, I hope I'm able to be as helpful for someone as you've been for me!

Cheers!

Brian_

Replies
0
Views
174
Replies
0
Views
526
Replies
9
Views
3K
Replies
4
Views
290
Replies
4
Views
701

1,190,693
Messages
5,982,327
Members
439,773
Latest member
tyruschen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back