+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    10-15-2017
    Location
    San Diego, California
    MS-Off Ver
    Excel 2016, Version 1705
    Posts
    3

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,689

    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.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Registered User
    Join Date
    10-15-2017
    Location
    San Diego, California
    MS-Off Ver
    Excel 2016, Version 1705
    Posts
    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. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,689

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-15-2017
    Location
    San Diego, California
    MS-Off Ver
    Excel 2016, Version 1705
    Posts
    3

    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. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,689

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

    What is the condition for break of 30 mts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Hours worked during shift differentials
    By alexanderears in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-02-2014, 11:24 AM
  2. Calculating actual hours worked only in core hours
    By Val C in forum Excel General
    Replies: 3
    Last Post: 02-27-2013, 01:54 AM
  3. [SOLVED] Calculating hours worked for 3rd shift
    By tiffany04530 in forum Excel General
    Replies: 10
    Last Post: 11-30-2012, 01:56 PM
  4. Replies: 4
    Last Post: 09-21-2012, 12:58 AM
  5. Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  6. Calculating Hours Worked from Shift Begin and Shift End
    By lukeflegg in forum Excel General
    Replies: 5
    Last Post: 08-12-2011, 03:25 PM
  7. Hours Worked Calculator
    By sammer021486 in forum Excel General
    Replies: 2
    Last Post: 08-08-2008, 02:41 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1