VLOOKUP to reference allowed break times

[Kmac]

I need some formula for columns M &N rows 83 - 119 to calculate the appropriate off or break time of employees. this formula needs to go into E103:E109 & K103:K109.


i tried using

=VLOOKUP(MINUTE($J$48)+HOUR($e103)*60,$M$84:$N$119,2,1)

but it didnt work


I need all kinds of help all around, anything you have will be appreciated.

[NBVC]

What should the result in those E103:E109 and K103:K109 be? and based exactly on what?

You have the same times listed in column M several times over with differing column N values... how do you know which to choose?

[Kmac]

column M was formatted wrong it has been corrected.

column m is the total time of any given shift. column in is the amount of time they are allowed to take breaks.

the total amount of time of their shifts are in e69:82 & k69:82

in e102:109 i need the total amount of excess break time.

in d102:109 it has the total amount of break time they had.

so basically in e102:109 i need to reference and also the m and n columns. and give the the total amount of time they are allowed based on the duration of their shift, and then subtract that from the number in d102:109.

[NBVC]

This is a guess...as I am not quite sure I understood.

In E103:

Please Login or Register  to view this content.

copied down.

If not right... tell me what the answers should be and how they are derived at.

[Kmac]

no that did not work it is giving me a #n/a error. here i made a simpler version, kind of.

E2:16 shows the duration of the shift they worked for every day of the week.

We allow employees "off time" or break time that our system tracks automatically, based on the duration of their shift. ie if they work 8 hours, they get 30 min of break time.

the break time chart is in column's G & H. G shows the the length they work and H shows how much break time they get for working that length.

so in E:20 i need it to show how i have it in the example there. it need to look at E3 to see how long the shift was, 7 hours. and then look at the break time chart and see that a 7 hour shift allows employees 26 minutes of break time. then it needs to subtract the allowed amount of break time from their total "off" or break time in D:20 giving me the total excess break time they took in E:20

[NBVC]

It would help if you first adjusted your ranges to suit your new data locations....

And second that you don't type extra spaces after Mon, Tues, etc... in your lookup table...

Also I added a row in the G:H data to account for when value in your top table are less than 4...

[Kmac]

no it is not working correctly. i changed D20 to 45 minutes and it showing 10 excess and that is wrong. it should show 11 and it is showing 10.

[Kmac]

bumpity bump bump

[NBVC]

I had kept your -"00:30" in the formula and not sure what purpose that had..

Maybe?

Please Login or Register  to view this content.