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

1. ## 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.

-Alex

2. ## Re: Actual Hours worked split into 3 shift parameters for Bartender Tip Calculator

In D10, then drag down.

=IFERROR(IF(OR(B10>0,B11>0),IF(OR(C10>0,C11>0),IF(C11>C10,C11-C10,1+C11-C10),IF(B11>B10,B11-B10,1+B11-B10))-IF(IF(OR(C10>0,C11>0),IF(C11>C10,C11-C10,1+C11-C10),IF(B11>B10,B11-B10,1+B11-B10))>TIMEVALUE("05:00:00"),"00:30:00",0),""),"")

It is not correct method using 2 rows for each name. Instead of that for SCHEDULED Column B and C , for ACTUAL column D and E is to be used. If it is ok the format of table can be changed.

3. ## Re: Actual Hours worked split into 3 shift parameters for Bartender Tip Calculator

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

4. ## Re: Actual Hours worked split into 3 shift parameters for Bartender Tip Calculator

For second part

In G10
=MAX(0,MIN(IF(\$C11<>"",IF(\$C11<\$C10,1+\$C11,\$C11),IF(\$B11<\$B10,1+\$B11,\$B11)),\$C\$4)-MAX(IF(\$C10<>"",\$C10,\$B10),\$B\$4))

In J10
=MAX(0,MIN(IF(\$C11<>"",IF(\$C11<\$C10,1+\$C11,\$C11),IF(\$B11<\$B10,1+\$B11,\$B11)),\$C\$5)-MAX(IF(\$C10<>"",\$C10,\$B10),\$B\$5))

In L10
=MAX(0,MIN(IF(\$C11<>"",IF(\$C11<\$C10,1+\$C11,\$C11),IF(\$B11<\$B10,1+\$B11,\$B11)),\$C\$6)-MAX(IF(\$C10<>"",\$C10,\$B10),\$B\$6))

Drag down al

5. ## Re: Actual Hours worked split into 3 shift parameters for Bartender Tip Calculator

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

6. ## Re: Actual Hours worked split into 3 shift parameters for Bartender Tip Calculator

What is the condition for break of 30 mts.

There are currently 1 users browsing this thread. (0 members and 1 guests)