In this article, we will create a custom function or UDF to calculate the shift adherence time for the agents.
Raw data for this example consists of agent id along with login and logout time of each agent for the day. We have also defined the shift start and shift end time.
We have used custom function “TimeInterval” to calculate shift adherence time. This function takes four parameters as input.
Function TimeInterval(Login, Logout, ShiftStartTime, ShiftEndTime)
Shift start time is defined in the cell C7 and shift end time is defined in the cell C8.
Logic explanation
To calculate shift adherence time, we have changed the login time to shift start time. If login time is early than shift start time, then shift start time will be considered as login time. If logout time is late than shift end time, then shift end time will be considered as logout time. Now, difference between the login and logout time will give shift adherence time.
Please follow below for the code
Option Explicit
Function TimeInterval(Login As Double, Logout As Double, _
ShiftStartTime As Double, ShiftEndTime As Double)
'Checking whether login time is less than logout time
If Login > Logout Then
TimeInterval = "Login time should be less than logout time"
Exit Function
End If
'If login is less than shift start time then changing login time to shift start time
If Login < ShiftStartTime Then Login = ShiftStartTime
'If logout is greater than shift end time then changing logout time to shift end time
If Logout > ShiftEndTime Then Logout = ShiftEndTime
TimeInterval = Logout - Login
End Function
If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Can u share the excel sheet with calculation of shift adherence with overtime or any delay login exception and break not more than one hour.
calculate it in your way but it will not go beyond the shift if there is exception then it will happen in calculation
like 05 minutes before shift login and after 5 min logout exception and break is in the schedule