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

    Calculation Field Question

    ingridblaauw

      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.

      Thanks in advance for any advice you can give me.

        • 1. Re: Calculation Field Question
          philmodjunk

          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
            ingridblaauw

            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
              philmodjunk

              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
                ingridblaauw

                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
                  philmodjunk

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