Excel formula for timekeeping

Hi Can anyone help me create formula for the attached file? I need to compute the total no. Of hours the employees, overtime , tardiness and undertime

BASIS:

FOR NIGHSHIFT EMPLOYEES
TIME IN MUST BE 16:00 WITH A GRACE PERIOD OF 10 MINUTES

FOR DAY SHIFT EMPLOYEES
TIME IN : 06:00 AM OR 07:00 AM DEPENDING ON THE SCHEDULE (SEE SCHEDULE SHEET) ALSO W/ A GRACE PERIOD OF 10 MINUTES

attempted in Attachment. formula ~ =IF(OR(I14="",L14=""),"Check Time",TEXT(+L14-I14-Grace,"Hh:mm")). I created a named Range 'Grace' for your 0:10 grace period. I do not know your Payroll weeks, so it's hard to determine OT. It is not written but I assume > 40 hour is OT? Some places get OT for hours worked past 8 in a day (can't tell from your details). I'm not a fan of the time layout with hidden rows. In general, I prefer Decimal time (using .25 for 15 minutes after, .50 for 30 minutes & .75 for 45 minutes) OR full time with date. I would add in a column for Payroll week number, this way you can easily determine OT (for Hours > 40). If it is more like a database, you can then put the data into a Pivot Table and summarize results.

Hi thank you so much. I appreciated your work. It's awesome