Limit cell value

[yuckypants]

I'm working on a spreadsheet that monitors overtime and doubletime. The formulas are correct and working, but it skews the numbers if the subject has more than 8 hours a day or more than 12 hours a day. Let me demonstrate (example)

A1=regular hours
A2=overtime hours
A3=doubletime hours

A1 has to equal 8 -- even if the subject worked more than 8 hours, I need the difference to go to A2. And again, if the subject worked more than 12 hours, I need the difference to go to A3. I was thinking of using conditional formatting, but I don't know how to create a macro for this. It may not even be possible. I was thinking that if I just state that the subject worked 14 hours for the day, that I could put that in a field and then have it broken down in the other fields, but again, beyond my expertise.

Thanks in advance.

[yuckypants]

Oh - and my formula looks like this:

=IF(AA5>12;AA5-12;0)

This one calculates doubletime.

[Uziel]

Maybe you could make a UDF? I always make UDFs when formulas become complex.

Just change the vHours to the total hours worked and vPayRate to hourly wages. If necessary more variable could be added to determine the cutoff for overtime and double time, but I assumed time up to 8 hours as standard, between 8 and 16 as overtime, and over 16 as doubletime.

Function OT(vHours As Double, vPayRate)
    Dim A1, A2, A3 As Double
    Select Case vHours
    Case Is <= 8                                  'This case is for when total hours is less than 8
        A1 = vPayRate * vHours
    Case 8 To 16                                  'This case is for when total hours is between 8 and 16
        A1 = vPayRate * 8
        A2 = vPayRate * 1.5 * (vHours - 8)
    Case Is > 16                                  'This case is for when total hours is more than 16
        A1 = vPayRate * 8                      'This is pay rate times 8 hours
        A2 = vPayRate * 1.5 * 8              'This is for paying over time at 1.5 times normal pay for 8 hours
        A3 = vPayRate * 2 * (vHours - 16) 'This is paying double time for hours over 16
    End Select
    OT = A1 + A2 + A3
End Function

[yuckypants]

It's a little over my head. I understand what you're doing, but I don't understand how to limit the hours entered in each field.

[yuckypants]

Reading up on it, I believe I need to incorporate the MIN/MAX function, but I just don't know how.

[shg]

If B1 contains a start time, and B2 contains an end time, then

Reg: =MIN("8:00", B2 - B1 + (B2<B1))

OT: =MIN("4:00", MAX(0, B2 - B1 + (B2<B1) - "8:00"))

DT: =MAX(0, B2 - B1 + (B2<B1) - "12:00")

The formulas assume that you don't work 24 or more hours straight.

[yuckypants]

The only problem with this is that we don't input our time this way. We have already done the calculation (0900-1730=8hrs). I just want to state that person A worked 13 hours on Monday. Example:

A1= 13 (total hours)
A2= 8 (regular hours)
A3=4 (overtime hours)
A4=1 (doubletime hour)

The only field that would be edited would be A1. The calculations would be done from that point forward. (I'll just protect the cells).

[shg]

Reg: =MIN("8:00", A1)

OT: =MIN("4:00", MAX(0, A1 - "8:00"))

DT: =MAX(0, A1 - "12:00")

[yuckypants]

Thanks - I'll check it out and reply back.

[yuckypants]

I figured it out - thanks to all that replied. I did not use the min/max commands as a surmised that i would.

Here's the formulas that I used:

I have an open field that I put the hours worked that day in - From that point it calculates reg time, overtime and dt (there are other fields too, just not important)

To calc reg time:=IF(AA6>=8;8;+AA6)
to calc overtime:=IF(AA6>=12;4;AA6-8)
to calc doubletime"=IF(AA6>12;AA6-12;0)

Thanks again

[yuckypants]

Wait - ****. there's a problem. If the calculated field is a zero, the overtime field will report it as -8. I need it to be zero.

Can anyone help?

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[yuckypants]

I deleted the unimportant empty fields to shrink the doc down a bit.

But here it is,

thanks.

[yuckypants]

Got it.

Here it is:

=IF(AA6=0;"0";IF(AA6>=12;"4";IF(AA6<12;AA6-8;"0")))

I needed to nest the IF functions. All good now.