Actual Hours worked split into 3 shift parameters for Bartender Tip Calculator

Hello,

First time posting here, but have used these forums for lots of help, so thank you in advance.

I'm trying to create a tip calculator that links to my employee's schedule in the same work book. Attached you will find the most current sheet i'm working with.

The cells in RED on SHEET "1" are what I need help with.

PROBLEM 1: The first 2 times(SCHEDULED and ACTUAL) (Column B and Column C) are the SCHEDULED in and out times (9am-12pm) and the second times are the ACTUAL in and out times (12pm-8:30pm). I'm trying to get the time in "Total Time" (Column D) to calculate the hours from the SCHEDULED times, but still be able to be overridden if ACTUAL times are manually plugged in. If not plugged in, then ACTUAL would be ignored. I am also trying to subtract 30m of time if a shift is over 5 hours. (I couldn't figure out how to do it, without the AM, PM, and MID shifts giving a negative number as shifts changed.

Side Note: "OFF" and "OFFR" are used to annotate scheduled time off, and time off requests. You will find this under the "Total Time" (Column D) cell.

PROBLEM 2: Based on the orange shift parameters, I am trying to calculate how many hours in each shift Rows: (AM "G", MID "K", PM "L") that the staff worked.

Thank you again in advance for your help.

-Alex

Thank you,

I adjusted the formula you gave me to read ......"0"),"0") at the end. It works great. Do you have any input on the other calculation? I'm trying to split the hours based on the AM, PM, and MID shifts.

-Alex

You guys are awesome!! Thank you.

Is there a way to include the 30m lunch break in the breakdown of AM MID and PM?

-Alex