Excel Question #46

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

[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

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.

[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?

[Betty]

Originally Posted by Moonliner

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?

There we go! Yay!

[Moonliner]

Originally Posted by Betty

There we go! Yay!

You probably already figured this out, but you need to make the same change to cells G13 and G21

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

[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?

[Betty]

Originally Posted by 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?

That's a good question - let me verify. (I'm pretty sure the wording "work week" doesn't mean any old days.)