3 Replies Latest reply on Jun 24, 2010 10:36 AM by philmodjunk

    Calculation Script  Steps help

    InvectusIlumni

      Title

      Calculation Script  Steps help

      Post

      Hi guys,

       

      I've designed a punch card solution in FM10 to help employees clock in and out.

       

      I have a calculation field (Hours_work) that outputs the payable work hours based information collected in a weekly report.

       

      If no clock-in time and/or clock-out time is registered, I want the Hours_work field to output 0. How do I do that? Also, does anyone see any issues with my script steps below?. 

       

      Here's the script:

      Round(
      (Hour( ClockOut_time - ClockIn_time )*60 + Minute( ClockOut_time - ClockIn_time)) / 60 - Hours_lunch;
      2)
      +
      If(
      GetAsNumber(ClockOut_time) <= GetAsNumber(ClockIn_time);
      24;
      0)

       

      Round(

      (Hour( ClockOut_time - ClockIn_time )*60 + Minute( ClockOut_time - ClockIn_time)) / 60 -

      Hours_lunch;

      2)

      +

      If

      (GetAsNumber(ClockOut_time) <= GetAsNumber(ClockIn_time);

      24;

      0)

       

      When the clock in time  is 5:29 PM and no clock-out time is specified, the Hours_work field outputs 6.52. That's wrong. How do I fix that?

       

      Thanks a lot

       

       

       

       

       

        • 1. Re: Calculation Script  Steps help
          philmodjunk

          Are these Time fields? TimeStamp fields would work better as you don't have to deal with midnight as a special case.

           

          Without trying to parse out all the details of your calculation, you need to first test for the empty fields and compute a value only if the fields contain data.

           

          case ( IsEmpty ( ClockOut_time ) ; 0 ;

                      IsEmpty ( ClockIn_time ) ; 0 ;

                      /* put your elapsed time calculation here */ )

           

          I don't see the reason for extracting hours and minutes and then adding them together here. The difference of two time or TimeStamp fields will give you the difference in seconds. I'd compute the difference in seconds, convert the seconds into whatever units are meaningful (probably hours) and then round the result to the desired precision (often the nearest 10th of an hour.)

           

          Round ( 

          case ( IsEmpty ( ClockOut_time ) ; 0 ;

                      IsEmpty ( ClockIn_time ) ; 0 ;

                      ( ClockOut_time - ClockIn_time ) / 3600 ) - Hours_lunch

                       ; 1 )

                      

          /* ClockOut_time and ClockIn_time must be time stamp fields */

          • 2. Re: Calculation Script  Steps help
            InvectusIlumni

             

            Thanks a lot for the quick follow-up.

             

            Just to make sure I get this right,  this is how I should have my final calculation:

             

            Round ( 

            case ( IsEmpty ( ClockOut_time ) ; 0 ;

                        IsEmpty ( ClockIn_time ) ; 0 ;

                        ( ClockOut_time - ClockIn_time ) / 3600 ) *60 - Hours_lunch

                         ; 1 )

            • 3. Re: Calculation Script  Steps help
              philmodjunk

              IF the fields are timestamp so that date and time are recorded in both fields and IF you want the result in hours rounded to the nearest 10th of an hour.