7 Replies Latest reply on Aug 2, 2012 12:02 AM by stephensexton

    Figuring Overtime

    cabarnes

      I have a calculation field that has the total hours worked. I have set up a Regular Time field and also a Overtime Field, both as calculations.

       

      In the Overtime field I have the following calculation:

      c_PayTime - c_RegHours

       

      In the Regular Time field I havethe following calculation:

      Case

      (

      c_PayTime ≥ "8:00" ; "8:00";

      c_PayTime < c_PayTime

      )

       

      This works if the PayTime is over 8 hours, but not if it is less than 8 hours.

       

      For example, if the PayTime is 9:30, I would like the Regular field to display 8:00 and the Overtime field to display 1:30. If the PayTime is 7:15, then the Regular field to display 7:15 and the Overtime field to display noting (or 0:00).

       

      Thanks,

      Craig

        • 1. Re: Figuring Overtime
          MarcDolley

          Each of your calculations only has a single argument, so you could just use an If() function instead of Case(). I also assume that c_PayTime is a calculation returning a numercial value (as opposed to a Time value)

           

          Regular Time =

          If(c_PayTime  ≥  8 ; 8; c_PayTime)

           

          Overtime =

          If(c_PayTime  >  8 ; c_PayTime - 8; 0)

           

          Hope that helps

           

          Marc

          1 of 1 people found this helpful
          • 2. Re: Figuring Overtime
            cabarnes

            That didn't do it. If I have 10:00 hours total, it now returns 12:00 for Regular Hours and 9:59 for Overtime.

             

            The calcs are set to time, not numbers. All of the time fields are time.

             

            If I change the 8's in your code to "8:00:00" or "8:00", I then get 10:00 for Regular and 12:00 for Overtime.

             

            Craig

            • 3. Re: Figuring Overtime
              MarcDolley

              The calcs should be numbers not time. Your calculation isn't calculating 8 hours, it's calculating 8:00 am.

               

              In a simple timesheet scenario, you have a start time field and an end time field. Both are time fields. Calculating the hours between the two does not result in a time, it results in a numerical value representing the number of hours (and perhaps minutes and seconds) between the two times.

               

              If you fix that problem, you'll find that my calculations work.

               

              Marc

              1 of 1 people found this helpful
              • 4. Re: Figuring Overtime
                stephensexton

                Try using the same calcs as suggested by Marc, except use Time (hh ; mm ; ss) to indicate the time - e.g.

                 

                Regular Time =

                If(c_PayTime  ≥  Time ( 8 ; 0 ; 0 ) ; Time ( 8 ; 0 ; 0 ) ; c_PayTime)

                 

                Overtime =

                If(c_PayTime  >  Time ( 8 ; 0 ; 0 ) ; c_PayTime - Time ( 8 ; 0 ; 0 ) ; Time ( 0 ; 0 ; 0 ) )

                 

                Instead of Time ( 0 ; 0 ; 0 ), you could also just use "" to leave the field empty.

                 

                ... and you may need to change how the time is displayed in the field.  In the inspector window under "data formatting" change the format to hhmm and then importantly use 24hr rather than 12hr format, so that it displays the number of hours correctly.

                 

                If there is a chance that you cross over midnight during a work shift, then it might be better to calculate the number of hours using a timestamp field rather than time.

                • 5. Re: Figuring Overtime
                  MarcDolley

                  Except that in your calculations, you're saying that if c_PayTime  ≥ 8 am. That's not what he's after. He's looking for c_PayTime  ≥ 8 hours.

                   

                  It's a simple misinterpretation of time versus hours. The OP needs to fix that part first to solve the main problem.

                   

                  Marc

                  • 6. Re: Figuring Overtime
                    cabarnes

                    Thanks, that did exactly what I needed and I did use "" to leave the field empty. Our shifts never go over midnight, but what you mentioned is good to know.

                    • 7. Re: Figuring Overtime
                      stephensexton

                      The time function can also be a useful way of identifying hours and minutes in a calculation as in this case, rather than just a time of day... but I see your point.  It is important to consider what the function is referring to.  This is why I suggested changing the time format to 24hrs in the inspector window (plus remove the AM/PM) so that the user doesn't mistake the value for a time of day.  Otherwise, 4:00 PM might be interpreted as 4 hrs instead of 16 hrs.