Measuring the time an employee deviates from a prescribed schedule

[Brian_]

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.

 

[alx7000]

Hi Brian,

Welcome to the board! If I understand correctly the following should work (assumes data is in rows 2-29):

Excel Workbook
	K
2	COMPLIANT
Sheet1

Excel 2010

Cell Formulas
Range	Formula
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_]

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_