UNDERTIME and LATE computation for night shifters

Hi!

I'm working on our company's timesheet.
I was able to compute the TARDINESS for our employees actual log ins vs scheduled log ins.
But then, I have this problem when the employee gets IN earlier than the midnight schedule.

Sample:
Schedule log in: 12:00 AM
Actual log in: 11:56 PM
Result: 23:56:00

Then, for UNDERTIME, I'm able to compute the undertime of the employee when I minus the SCHEDULED LOG OUT to ACTUAL LOG OUT, but then, when the ACTUAL LOG OUT is greater than the SCHEDULED LOG OUT, it shows 23:00:00...

Sample:
Scheduled log out: 7:00:00 AM
Actual log out: 7:05:00 AM
Result: 23:55:00

Hope you can help me here. I've attached the sample file. I put on a comment for the needed help.
Thank you so much!

One possible approach, adapting pertinent section of your existing formulae.

Formula:

Please Login or Register  to view this content.

introduction of MOD will ensure Shift End times remain time-only, this will fix your undertime calculation (e.g. 9pm + 09:00 = 0.25 rather than 1.25)

Formula:

Please Login or Register  to view this content.

additional OR test designed to identify where login time is >=12 hours of shift start and treats as valid (i.e. login precedes shift start time of midnight)

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

tine1112 welcome to the forum

Perhaps you missed Ali's post immediately above yours, so I will repeat it for you...
Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread