5 Replies Latest reply on Apr 25, 2011 2:23 PM by philmodjunk

# Calculation Field Question

### Title

Calculation Field Question

### Post

I'd like to create a calculation field that will give me a total of hours when a work week goes over 37.5 hours total, but if the weekly total goes over 40, will give me a max total of 2.5 hours.  I've already got the calculation for when the hours go over 40.  I've tried embedded If statements, as well as Case statements, but I'm just not getting it right.

• ###### 1. Re: Calculation Field Question

Seems like your post describes three ranges of values: 0 - 37.5, 37.5+ to 40, 40+, but only specify results for the second two ranges.

What result to you want for 0 - 37.5 total hours?

You can use this case statement, but insert the expression you need where I put a place holder behind // in it:

Case ( Total Hours < 37.5 ; //put result for this range here ;
Total Hours < 40 ; // Total Hours calc here ;
Min ( //Total Hours Calc here ; 42.5 )
)

• ###### 2. Re: Calculation Field Question

Here's what I was trying:

Case(GetAsNumber(TotalDaily W1) > 40; TotalDaily W1 - 37.5; 0)

This is giving me the total hours, including anything over 40.  It's for a timesheet, so the pay for hours between 37.5 and 40 is at straight time, and over 40 is time and a half. I only need the range for 375.-40, as my other calculations are working fine.  For example, for a week with hours totaling 40.5, I would want this calculated value to equal 2.5, as the .5 hours over 40 is already calculated elsewhere for the time and a half total.

Thanks.

• ###### 3. Re: Calculation Field Question

So it fails for you because you get results that are greater than the 2.5 maximum?

If so, use: Min ( 2.5 ; TotalDaily W1 - 37.5 ) inside your Case function.

(And you shouldn't need GetAsNumber here. TotalDaily W1 should already be either of type number or a calculation field with number as its return type.)

• ###### 4. Re: Calculation Field Question

Hmmmm.

I'm just not getting it.  I've tried this:

Case(TotalDaily W1 > 40; TotalDaily W1 - 37.5; Min ( 2.5 ; TotalDaily W1 - 37.5 );0)

Gives me a total of 3 for a weekly total of 40.5.

And I've tried this:

Case(Min ( 2.5 ; TotalDaily W1 - 37.5 );0)

Gives me a 0. Sorry to be obtuse....

• ###### 5. Re: Calculation Field Question

Case(TotalDaily W1 > 40;  Min ( 2.5 ; TotalDaily W1 - 37.5 );0)