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!!!!!

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 ?

A better laid out sheet would help you significantly.

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%

Originally Posted by mcabrera25

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)

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

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.

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.

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.