Need help with some complicated time card math

[BestBoy]

We work under a union contract that pays a penalty if we are not broken for a meal after 6 hours. If we work a long day (which is not unusual) we go into Meal Penalty again 6 hours after we are back in from lunch. There is an additional penalty every half hour after the meal is due until a meal break is called. The first two penalties are fixed amounts ($10 & $15). The third and all subsequent Meal Penalties are an hour of pay at the rate you are at when the penalty occurs (we call them "prevailing rate penalties"). In other words, if you are in time and a half when that third penalty occurs, you get an hour of pay at time and a half. If we are in double-time, the penalty is an hour of pay at double-time.

Our overtime structure is:
First 8 hours - straight time
8-12 hours - 1.5x
12-14 hours - 2x
14+ - 2.5x

The problem I am having is coming up with an expression in Excel to calculate the dollar amount of Meal Penalties for that second meal. Because the time we break for lunch is variable, the time from when we are back on the clock from lunch until we go into overtime is never the same. Can anyone help?

As an example, let's say our hours for a given day are:
Start work at 7:00am
We break for lunch from 1:25 - 1:55pm
We finish work at 10:24pm

In this example, we were due our lunch break at 1:00pm (6 hours after we start work) so we would get 1 Lunch Penalty.

We were back on the clock from lunch at 1:55pm so we were due a 2nd Meal break at 7:55pm. We would incur 2nd Meal Penalties starting then and every half hour after that until the end of the work day totaling 5. The penalties would occur at:
7:55 = $10
8:25 = $15
8:55 = 1 hour of 2x
9:25 = 1 hour of 2x
9:55 = 1 hour of 2.5x

The first two penalties are still fixed amounts. We go into double time after twelve hours, which is 7:30pm, and double and a half time at 9:30pm (the half hour lunch is off the clock). The third and forth penalties occur after 12 hours so they are each an hour of double time. The fourth occurs after 14 hours so it is at double and a half time.

In my current version of this spreadsheet I have the following columns assigned:
B - Date
C - Start Time
D - Break for lunch
E - Back in from lunch
H - Dismissal time
I - Base pay rate for an 8 hour day
J - Total Hours worked that day (rounded to the next 1/10 of an hour)
K - Time off the clock for lunch (either 1/2 hour or 1 hour)
Q - Number of Lunch Penalties
S - Number of 2nd Meal Penalties

I have omitted columns not involved in the calculation.

The only thing to simplify this a little is that the prevailing rate 2nd Meal Penalties will always be at least time and a half. The approach I was taking was a series of cumulative IF statements. This is what I have so far:

=(IF(S2>0,10.00,0))+IF(S2>1,15.00,0)+IF(S2>2,(S2-2)*((I2/8)*1.5),0)+

Since we know that 2nd Meal penalties will always be at least an hour of time and a half, I was going to add an additional half hour of pay for each penalty occurring after 12 hours, and then another half hour of pay for any penalties occurring after 14 hours to make up the difference between time and a half, double time, and double and a half time. But I am having no luck coming up with a way to compare the two variable times of overtime and penalty that wouldn't involve an expression for each individual penalty. Though theoretically you could have infinite penalties, in practice the most you ever might see could be 40-50 in an extreme circumstance. Dealing with them individually would be a huge expression.

Thanks for any help you can offer!

 

[Jobowo]

The first two penalties are still fixed amounts. We go into double time after twelve hours, which is 7:30pm, and double and a half time at 9:30pm (the half hour lunch is off the clock). The third and forth penalties occur after 12 hours so they are each an hour of double time. The fourth occurs after 14 hours so it is at double and a half time.

Is this it? If so just add

.... +IF(S2>3,(S2-3)*2.5*I2,0)

to the end of the formula you have. It's ugly but it works

If you want to be a little more succinct you could use:

=10*(S2>0)+15*(S2>1)+(S2>2)*1.5*I2+(S2>3)*(S2-3)*2.5*I2

 

[Jobowo]

Oops, no.. Your description is confusing. According to that paragraph 3rd and 4th penalties are at double time in one instance, and then "the 4th occurs after 14 hours so it is 2.5 x..." which is confusing.

If you can definitely determine the rate multiplier up to a maximum multiplier value for any given penalty number then your problem can be solved by some variation of the above. But maybe your problem is that from the information given, you can't tell whether the 4th penalty is at 1.5x or 2x or 2.5x, which is something that Excel can't solve.

 

[BestBoy]

Thanks for the response!

Sorry for the typo. It should read the 5th penalty occurs after 14 hours.

Though your expression works for the example, it won't for others. I think you grasp the problem though. That's why I am having so much difficulty. If I were writing a full-on program, I could create some kind of iterative loop to figure out the rate for each penalty individually. It seems like there should be a way to have excel compare the two variables or some more elegant way to express the math, but I'll be damned if i can come up with it. You may be right, it may be beyond excel's capabilities.

If I can figure this out, next we figure out how to calculate invaded turnaround!

 

[Jobowo]

Well there may be something missing from your description. If you can figure out which rate applies in your head then Excel will be able to calculate it. There may be something buried in your description which is obvious to you and tells you how to figure out which rates apply when. Can you try to explain more clearly how you know whether the rate is 1, 1.5, 2 or 2.5?

 

[BestBoy]

I know, it's a complicated system. I have no idea who came up with it.

The two variables that must be compared to determine what rate a prevailing rate penalty is at are

a) the number of worked hours since the start of the day

and

b) the time the penalty occurs.

Though the time we go into different rates of overtime is constant, the time we incur a penalty isn't. When I am figuring out prevailing rate penalties on paper, I first draw a time line of the day to figure out when we go into different overtime rates. So I know the times we go into time and a half (8 hours after start time), double time (4 hours later), and double and a half time (2 hours after that). I would then plot the meal breaks and any penalties against that time line to see what rate we are in when any of the prevailing rate penalties occurred.

In the sample day I gave in my post, the first time line would start at 7am (time we started work) and have marks at:
3:30pm (8 hours, the beginning of time and a half),
7:30pm (the time we go into double time),
9:30pm (the time we go into double and a half time) and
10:24pm (the end of our work day).

Just to clarify, though penalties occur every half hour, the time they start is what determines what rate applies. Any prevailing rate penalties for that day that fell between 3:30pm and 7:30pm would be an hour of time and a half. Any that fell between 7:30pm and 9:30pm would be an hour of double time. Any that fell after 9:30pm would be an hour of double and a half time.

I'm heading out of town so I don't have the time to make a more graphic representation of the time lines for you to look at right this minute. But if that would help explain it better, I'll make one up on Sunday night when I get back in town.

Thanks for bearing with me through this confusing project.

 

[Jobowo]

OK, I think I've got it now that I have time to get my mind around it and have read your description more closely. This can be done with a series of IFs but will see if I can come up with something more elegant when I get a moment...or two.

 

[Jobowo]

This is going to take longer to describe than it took to solve the problem, and ironically the easiest solution requires the longest description! I've got to 3rd base but this might need some final tweaking (notes below):

- p is the number of penalties
- The essence of the solution is that you need to work backwards from the total number of hours worked, adding penalty hours for the going rate, then subtracting 1 from p and .5 from the total hours to get a new going rate, until p = 2. At that point you just add the last two penalties.

Now to solve this, you could get into an extremely ugly solution with either multiple and highly complex IFs or possibly an array formulae to boggle the mind, or....

Simple pre-construct two lookup tables as follows, on a separate sheet

1. Time/OT multiplier:
COL A COL B
Hrs Mult
0 1
8 1.5
12 2
14 2.5

Name this table area RMult. This is a simple table to provide the OT rate after hrs worked

2. A larger table that precalculates, given total hours worked and number of penalties, what the total penalty hours are:

- Create a column of numbers 1-12 in D2:D13. This is the number of penalties. If you get more than 12, simply keep going
- Create in E1:U1 a row of numbers, beginning with 18 and decreasing by 0.5 down to 10. This is the total hours worked, starting at 18 and decreasing to 10 (I figured that this would be the lowest hours total that would incur 2nd meal penalties.
- Create a name for the top row (E1:U1) "TotHrs"

- In cell E2, enter the formula: =VLOOKUP(E$1-$D2*.5,RMult,2)
- In cell E3 =VLOOKUP(E$1-$D3*.5,RMult,2)+E2
- Drag that second formula down to the bottom of the column
- Drag the whole column across to the end of the table (col U)
- Create a name for the data area of the table "Penalties"

3. You've now done most of the work! The only remaining "problem" is the formula for calculating penalties but that is now relatively easy:
=IF(S2>0,10,0)+IF(S2>1,15,0)+I2*IF(S2>2,INDEX(Penalties,S2-2,MATCH(J2,TotHrs,1)),0)

You'll need to understand and tweak this a little. I suspect there may be a niggling little problem that may occur if the end of lunch falls exactly on the half hour and I don't know the answer to that. I may also have some typos in here.

Some notes:
- I've used named ranges because it is easier to read them in contexts like this. If you need help with this let me know
- The formula: the "MATCH" looks up the total hours worked in the top
row of the Penalties table and finds the column number. S2-2 gives the
row number and INDEX(Penalties, row, col) finds the value in the
table corresponding to those values. Since this now contains the total
penalty hours you have precalculated, I2 * that gives you the rate.

 

[Jobowo]

What a mental blunder I've made! This doesn't work! Or at least it doesn't work when there is a second meal. Duh!

Thankfully, the general method does. The "Total Hours Worked" isn't the cap/reference you need to work with; it is the time of the second meal, or rather the hours worked until the second meal: (lunch time-start time)+6+(n-1)*0.5 [the n-1 is because a penalty occurs at the 6 hour mark]. Assume you could do this calculation in a hidden column T.

Calculate T2
T2 = D2-C2+6+(S2-1)*0.5 (you may need to adjust this a little--add or subtract 1 minute--for that 'edge' problem)

and then in your "$2nd Meal Penalty" column:
=IF(S2>0,10,0)+IF(S2>1,15,0)+I2*IF(S2>2,INDEX(Penalties,S2-2,MATCH(J2,T2,1)),0)