Deficit and Over hours for Shift Adherence

Hi guys, how are you?

I would like to ask for some help to put in practice an if formula from excel.

I have to send daily a shift adherence from some analysts and we have different shifts and also buffer addition for 5 minutes late and 5 minutes overtime.

12:00:00 - 21:00:00
13:00:00 - 22:00:00
14:00:00 - 23:00:00




Deficit: He has a logging buffer for 5 minutes, meaning that the logging stamp has to be until 12:05:00 and after that start to count deficit time.
Also if he logs out earlier from his shift this is calculated in his deficit time.

Overtime: Would show the time after 21:05:00


For example.
This analyst works from 12:00:00 until 21:00:00

Screenshot 2021-09-07 at 12.37.26.png

Today he arrived late at 12:25:00, so already subtracting the agreed buffer of 5 minutes, he has a deficit of 20 minutes.
He worked overtime 21:37:00,s so already subtracting the agreed buffer of 5 minutes, he has 32 minutes overtime.

I would like to have some assistance to put a formulation inside of Deficit and over time, subtracting the buffer, because I'm doing this manually, and is consuming a lot of time.

The idea is to provide weekly to the management team how many analysts are having deficit time and overtime, not approved.

Thank you

Let me see if I can upload the file.

Hi Jan,

Thank you for replying.
That totally makes sense and I will share the info with the management

OMG <3 THANK YOU VERY MUCH.
I wasn´t thinking about using it this way I was stuck with IF.
That's awesome.

One more question Jete, As mentioned above by Jan, I think what was said make totally sense, if the time pass through 12:05, the 5 minutes buffer should be ignored and summed in the time as well.

How does that could be implemented on here =SUM(MAX(0,A2-J1),MAX(0,L1-B2))

I added another collum with 00:05:00 and added it to your formula =SUM(MAX(0;A2-J1);MAX(0;L1-B2)+M1)

Do you think that would be OK?