+ Reply to Thread
Results 1 to 9 of 9

Break and Lunch overage based on Staffed time

  1. #1
    Registered User
    Join Date
    06-17-2016
    Location
    Las Vegas NV
    MS-Off Ver
    MS Office 365
    Posts
    6

    Cool Break and Lunch overage based on Staffed time

    Please provide a bit of guidance and help. I came across a spreadsheet that has some formulas to validate if an agent went over the allowed break or lunch time based on Staffed time. Could someone help me figure out how to add to the formula. Specifically in between the shift minutes.(i.e under 240 min comes back as 0) i.e(a cell that has between 241 and 299)
    My spreadheet contains the staffed time in seconds, break and lunchtime in seconds. I need to figure out if based on the staffed time they went over the allotted amount of time allowed and the % they went over. I attached a spreadsheet for viewing. C12-staffed time/ D12 Lunch time/ I12 contains formula. The Tables for Breaks, lunch and preshift are on top. Im an Excel newbie, so any help would be appreciated....
    Thank you for your help!!!!! This place Rocks!!!!!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    500

    Re: Break and Lunch overage based on Staffed time

    Based on your example it is difficult to understand exactly what it is you are looking for. Could you add more entries to your 'agent' list showing a few 'expected' outcomes and showing where exactly the 241 - 299 comes in the equation. Do you want to 'add' into the existing formula or create a 'new' formula ?

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Break and Lunch overage based on Staffed time

    A better laid out sheet would help you significantly.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-17-2016
    Location
    Las Vegas NV
    MS-Off Ver
    MS Office 365
    Posts
    6

    Cool Re: Break and Lunch overage based on Staffed time

    Thank you for your responses. I have been out on vacation.
    I added more sample names to the spreadsheet from the first response. I am using Breaks as the example, but I need the same for lunch. Pre shift is the same allowed time across the board.
    The example I need fixed in the formula is the following.
    If an agent works a shift of 6 hrs( i.e 361 minutes) and takes a break of 30 min. He should be at 0% since he is within the allotted allowed time. The formula shows he is over 50%.
    So the formula should include the time between 360 min and 599 min which allows a break time of 30 min; but once it hits 600 min the allowed break time allowed is 35 min.
    I would like the percentages to show the percent that the agent went over the allowed time based on the staffed time worked.

    If an agent works a shift of 10hrs( i.e 658 minutes) and takes a break of 35 min. He should be at 0% over since he is within the allotted allowed time. The formula shows he is under -22%

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Break and Lunch overage based on Staffed time

    Quote Originally Posted by mcabrera25 View Post
    If an agent works a shift of 6 hrs( i.e 361 minutes) and takes a break of 30 min. He should be at 0% since he is within the allotted allowed time. The formula shows he is over 50%.
    So the formula should include the time between 360 min and 599 min which allows a break time of 30 min; but once it hits 600 min the allowed break time allowed is 35 min.
    I would like the percentages to show the percent that the agent went over the allowed time based on the staffed time worked.
    Have you looked at the revised layout that i suggested? It gives the results that you say are expected based on the above example.

    I did leave the negative results for breaks under allotted time deliberately, inadequate breaks can be detrimental to an employees health.

    The negatives can be ignored by wrapping the formula in MAX, using the lunch formula from my suggestion as an example

    =MAX((D3-LOOKUP($C3,$O$2:$O$10,P$2:P$10))*(1/LOOKUP($C3,$O$2:$O$10,P$2:P$10)),0)

  6. #6
    Registered User
    Join Date
    06-17-2016
    Location
    Las Vegas NV
    MS-Off Ver
    MS Office 365
    Posts
    6

    Re: Break and Lunch overage based on Staffed time

    Jason.b75,
    Thank you so much for your help and input. Your cleaning of the spreadsheet and formulas works perfect.
    Could I just ask if within this sheet I could grab the percentage they went over and convert it to actual min and sec they went over in another cell?Maybe too much to ask for?
    Either way, thank you for your help and also BlindAlley.....

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Break and Lunch overage based on Staffed time

    That's easy enough, part of the formula already does that. Using the formula from post #5 as an example.

    =MAX((D3-LOOKUP($C3,$O$2:$O$10,P$2:P$10))*(1/LOOKUP($C3,$O$2:$O$10,P$2:P$10)),0)

    The part in bold calculates the difference in minutes, the second part converts it to percentage. (The formulas for lunch, break and preshift all use the same method).

    Use that as a formula to show the difference in minutes, including negative values for breaks taken below allocation, or with MAX to zero out any negative results.

    I'll let you make the changes, if you can do it then it might help you to understand how the formula works a bit better. Don't be afraid to ask if you get stuck.

  8. #8
    Registered User
    Join Date
    06-17-2016
    Location
    Las Vegas NV
    MS-Off Ver
    MS Office 365
    Posts
    6

    Re: Break and Lunch overage based on Staffed time

    Jason.b75,
    Thank you for your help. I guess I need help. I think I understand the formula, I am just not sure how the 2nd part converts it to %. I messed around with different variations and I still cant get it. Your help is appreciated.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Break and Lunch overage based on Staffed time

    Breaking the formula down

    LOOKUP($C3,$O$2:$O$10,P$2:P$10) finds the allotted time in the table by looking for the time entered into C3 in O2:O10 (by default the function returns the highest value that is less than or equal to the search value), then returning the allotted time in P2:P10.

    D3 (time taken) minus the result of the lookup function (time allowed) gives the difference in minutes, with a negative result if the time taken is less than allowed.

    In the second part 1 (in excel 1 is equal to 100% 0.5 = 50 %, etc.) divided by the result of the lookup function (time allowed) gives us 1 minute expressed as percentage of the time allowed.

    Multiplying the difference in minutes by the percentage value of 1 minute gives the overall percentage difference in the format of actual time taken as percentage of time allowed.

    =(D3-LOOKUP($C3,$O$2:$O$10,P$2:P$10)) will give minutes difference with negatives where applicable.

    =MAX((D3-LOOKUP($C3,$O$2:$O$10,P$2:P$10)),0) will give minutes difference with no negatives.

    Either of the above formulas multiplied by (1/D3-LOOKUP($C3,$O$2:$O$10,P$2:P$10)) will give the percentage difference.

    Hope that makes sense.

+ 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. Replies: 7
    Last Post: 04-25-2020, 03:23 AM
  2. macro to assign break and lunch schedules based on an 8hr shift
    By Shellybelly in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-09-2017, 09:29 AM
  3. Lunch & Break Timetable
    By ayrshiredj in forum Excel General
    Replies: 1
    Last Post: 06-08-2014, 12:19 AM
  4. lunch break times
    By exodus27 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2014, 06:48 PM
  5. Can't pull someone's lunch and break at a given time
    By Shellybelly in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2013, 09:01 AM
  6. Replies: 0
    Last Post: 08-21-2010, 08:03 AM
  7. Calculating time, potential lunch break
    By B1123 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-13-2008, 09:18 AM

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