Excel Question #46

[Betty]

I have a spreadsheet I got online and tweaked a bit for my husband to help him calculate overtime hours. Unfortunately it's not working right and now is not the time for this to be happening. ARGH! (It is SO one of those days - starting last night with the dead battery, continuing with today's flat tire and now this. While I won't go into details - the timing of this couldn't be worse.)

I'm hoping one of you excel-lent excel-lers will be able to look at it and go - add this and "ta-da" it works.

Here is a link to screen shots: https://picasaweb.google.com/1106550...JHNsqmhrKD1Kg#

Or I could send the file if that's easier.

It should be showing overtime on saturday if M through S has 8 hours. It's not though.

[Alex]

I'm not entirely sure of the goal, so is this correct?

1. You enter hours worked on a day on Row 4. If this is 8 hours or less it automatically appears on row 5 as regular hours. If it is more than 8 hours the first 8 hours goes under regular pay and the rest goes under OT. (Not sure what DT is.)

2. Once the total hours in a week reaches 40 hours then all hours after that go to OT.

Is that correct?

[Moonliner]

Originally Posted by Betty

I have a spreadsheet I got online and tweaked a bit for my husband to help him calculate overtime hours. Unfortunately it's not working right and now is not the time for this to be happening. ARGH! (It is SO one of those days - starting last night with the dead battery, continuing with today's flat tire and now this. While I won't go into details - the timing of this couldn't be worse.)

I'm hoping one of you excel-lent excel-lers will be able to look at it and go - add this and "ta-da" it works.

Here is a link to screen shots: https://picasaweb.google.com/1106550...JHNsqmhrKD1Kg#

Or I could send the file if that's easier.

It should be showing overtime on saturday if M through S has 8 hours. It's not though.

For starters, Monday is in column 'B' but the formula starts in column 'C'. Update your SUM function. (Note 'SUM' appears twice in the first image.)

[Alex]

But if I'm reading the formula correct what you have is doing this logic:

1. Is the value entered for each day Monday (B4) through Sunday (H4) greater than zero?

RESPONSE: FALSE, Sunday (H4) has a value of 0.

2. Then is the sum of hours from Monday (B5) through Saturday (G5) less than 40?

RESPONSE: FALSE, you have 8 hours in each of those six days which equals 48 hours.

Therefore: Value of H5 is zero.

Not sure again what you're trying to get to since if you flipped around the first false into a TRUE the result is also zero. And if you flipped around the second false into a TRUE you'd get the least of 8, -8, and 0, which would be -8.

[Alex]

Oh there were two pictures.

Ok, in cell G5 the problem is that you have the SUM going C4:F4 instead of B4:F4. With what you have you end up asking "what is the minimum of 8, 8, and 8).

Once you fix that sum you're going to get 0 in G5.

[Betty]

Originally Posted by Alex

I'm not entirely sure of the goal, so is this correct?

1. You enter hours worked on a day on Row 4. If this is 8 hours or less it automatically appears on row 5 as regular hours. If it is more than 8 hours the first 8 hours goes under regular pay and the rest goes under OT. (Not sure what DT is.)

2. Once the total hours in a week reaches 40 hours then all hours after that go to OT.

Is that correct?

DT is double time.

This spreadsheet this trying to calculate how many regular hours, overtime and doubletime hours in a pay period (which varies from month to month with payday on the 10th and 25th.)

The rules are:

One and one-half times the employee's regular rate of pay for all hours worked in excess of eight hours up to and including 12 hours in any workday, and for the first eight hours worked on the seventh consecutive day of work in a workweek; and
Double the employee's regular rate of pay for all hours worked in excess of 12 hours in any workday and for all hours worked in excess of eight on the seventh consecutive day of work in a workweek.

[Betty]

Originally Posted by Alex

Oh there were two pictures.

Ok, in cell G5 the problem is that you have the SUM going C4:F4 instead of B4:F4. With what you have you end up asking "what is the minimum of 8, 8, and 8).

Once you fix that sum you're going to get 0 in G5.

Cell G5 is =IF(SUM(C5:F5)<40,MIN(MIN(8,40-SUM(C5:F5)),G4),0)

I'm not seeing C4:F4 - or am I missing something.

[Betty]

He wants to be able to type in the total hours worked each day (the red number cells are the only part he will fill in) and come up with a total for the pay period to compare against some other software they use that (seemingly) randomly does weird things to hour calculations.

A lot of help I am giving him something that doesn't work. oops.

[Moonliner]

Originally Posted by Betty

Cell G5 is =IF(SUM(C5:F5)<40,MIN(MIN(8,40-SUM(C5:F5)),G4),0)

I'm not seeing C4:F4 - or am I missing something.

Cell G5 should read: is =IF(SUM(B5:F5)<40,MIN(MIN(8,40-SUM(B5:F5)),G4),0)

What is the formula for cell G6 and G7?

[Alex]

So the seven consecutive day rule only applies to calendar week? Working 8 days in a row from Wednesday through the next Wednesday would not?