13 Replies Latest reply: Jun 17, 2009 7:33 AM by derrick@work RSS

    TimeSheet to round to nearest quarter hour

    derrick@work Community Member

      I have this time sheet that has four "In" and four "Out" columns,and calculates hours worked in a two week pay period. There is a column on the right that calculates the hours worked for the day (see script below). I need it to round to the nearest quarter hour i.e.,

       

      If an employee arrives or leaves between:

       

      ":00" to ":07" minutes after the hour, calculate from the top of the hour

       

      ":08" to ":22" minutes after the hour, calculate from quarter after the hour

       

      ":23" to ":37" minutes after the hour, calculate from the half hour

       

      ":38" to ":52" minutes after the hour, calculate from three quarters past the hour

       

      ":53" to ":60" minutes after the hour, calculate from the top of the hour

       


      Examples:

       

      An employee records that they arrived at 8:07 a.m. Calculate from 8:00

       

      An employee records that they arrived at 8:08 a.m. Calculate from 8:15

       

      An employee records that they arrived at 8:22 a.m. Calculate from 8:30

       

      An employee records that they arrived at 8:37 a.m. Calculate from 8:45

       

      An employee records that they arrived at 8:53 a.m. Calculate from 9:00

       

      Script in "Hours Worked" column


      // compute block 0
      var StartInterval = 0
      if(HasValue(OUTA1[0]) and HasValue(INA1[0])) then
      StartInterval = Time2Num(OUTA1[0].formattedValue, "HH:MM") - Time2Num(INA1[0].formattedValue, "HH:MM")
      endif

       


      // compute block 1
      var LunchInterval = 0
      if(HasValue(OUTA1[1]) and HasValue(INA1[1])) then
      LunchInterval = Time2Num(OUTA1[1].formattedValue, "HH:MM") - Time2Num(INA1[1].formattedValue, "HH:MM")
      endif

       


      // compute block 2
      var EndInterval = 0
      if(HasValue(OUTA1[2]) and HasValue(INA1[2])) then
      EndInterval = Time2Num(OUTA1[2].formattedValue, "HH:MM") - Time2Num(INA1[2].formattedValue, "HH:MM")
      endif

       


      // compute total time in hours from the millisecond value
      Round(Sum(StartInterval, LunchInterval, EndInterval) / 3600000,2)

       

      I know in excel I can use =(ROUND(B2*96, 0)/96)-(ROUND(A2*96, 0)/96) to get the results I want. So I tried entering in something similar into the FormCalc script but with no luck. If you could tell me what I need to do to get this to work I'd be very appreciative. Thank you, Derrick

        • 1. Re: TimeSheet to round to nearest quarter hour
          derrick@work Community Member

          I got this solution from another forum, but because I know nothing about JavaScript it makes no sense to me. Would this be able to work for me? If so, could you explain how this script works? Thanks Derrick.

           

          function TimeDiff(cInName, cOutName) {
          // Using a time format of 'h:MM tt' for the start and end times
          // create JavaScript date time object for start time
          var oStart = util.scand("yyyy/mm/dd h:MM tt", util.printd("yyyy/mm/dd ", new Date()) + this.getField(cInName).value);

           

          // create JavaScript date time object for end time
          var oEnd = util.scand("yyyy/mm/dd h:MM tt", util.printd("yyyy/mm/dd ", new Date()) + this.getField(cOutName).value);

           

          // adjust date for start time if start time is later than the end time to the previous day
          if (oEnd.valueOf() < oStart.valueOf()) {
          oStart.setDate(oStart.getDate() - 1);
          } // end adjust start date

           

          // convert Times to minutes
          var StartTime = (oStart.valueOf() / (1000 * 60));
          var EndTime = (oEnd.valueOf() / (1000 * 60));
          // round Times to nearest 15 min
          StartTime = Round2Nearest(StartTime, 15);
          EndTime = Round2Nearest(EndTime, 15);
          // convert Times to hours
          StartTime = StartTime / 60;
          EndTime = EndTime / 60;
          // round Times to 1/100 th of an hour
          StartTime = Round2Nearest(StartTime, 0.01);
          EndTime = Round2Nearest(EndTime, 0.01);
          // compute difference in Times
          Diff = (EndTime - StartTime)
          // return computed difference in hours
          return Diff
          } // end TimeDiff
          // end document level functions

          • 2. Re: TimeSheet to round to nearest quarter hour
            GKaiseril CommunityMVP

            It is clearly for Acrobat's AcroForms and not LiveCycle Designer forms. It is a document level user defined JavaScript funciton. You call the function with the start time and end time fields. The function then uses the field names to get the field values and convert those strings to the value of the JavaScript'x date time object. For each date time object a second user function is called to round the values to the nearest 15 minutes, quarter hour.

             

            You do not have the code for the second user defined function.

             

            The function then converts the rounded time values to hours and then computes the difference and returns this difference to the location where the functions was called from.

             

            A function that takes variable input and returns a result is used because within a time sheet, one could have from 5 to 28 repatitions of this type of calculation with only the name of the imput fields being changed.

             

            The code can be simplier in LiveCycle Designer because LiveCycle Designer FormCalc does some of the work that one needs to do in JavaScript. But one could modify this code to work in LiveCycle Designer JavaScript.

            • 3. Re: TimeSheet to round to nearest quarter hour
              derrick@work Community Member

              Thanks for the reply, any idea on how to do quarter hour rounding in FormCalc than?

              • 4. Re: TimeSheet to round to nearest quarter hour
                Varma_LC Community Member

                I would change your FormCalc script to look like this..........

                 

                var timeStamp
                var timeOut
                var timeIn
                var Cat
                var timep1
                var timep2

                 

                var StartInterval

                if (HasValue(OUTA1[0]) and HasValue(INA1[0])) {
                timeStamp = OUTA1[0].rawValue //OUTA1[0].formattedValue
                Cat = At(timeStamp,":")
                timep1 =  Left(timeStamp, Cat-1)
                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
                  timeOut = Concat(timep1, ":", "00")
                }
                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
                  timeOut = Concat(timep1, ":", "15")
                }
                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
                  timeOut = Concat(timep1, ":", "30")
                }
                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
                  timeOut = Concat(timep1, ":", "45")
                }
                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
                  timeOut = Concat(timep1+1, ":", "00")
                }

                timeStamp = INA1[0].rawValue //INA1[0].formattedValue
                Cat = At(timeStamp,":")
                timep1 =  Left(timeStamp, Cat-1)
                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
                  timeIn = Concat(timep1, ":", "00")
                }
                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
                  timeIn = Concat(timep1, ":", "15")
                }
                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
                  timeIn = Concat(timep1, ":", "30")
                }
                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
                  timeIn = Concat(timep1, ":", "45")
                }
                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
                  timeIn = Concat(timep1+1, ":", "00")
                }

                StartInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                }else {
                StartInterval = 0
                }


                var LunchInterval

                if (HasValue(OUTA1[1]) and HasValue(INA1[1])) {
                timeStamp = OUTA1[1].rawValue //OUTA1[1].formattedValue
                Cat = At(timeStamp,":")
                timep1 =  Left(timeStamp, Cat-1)
                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
                  timeOut = Concat(timep1, ":", "00")
                }
                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
                  timeOut = Concat(timep1, ":", "15")
                }
                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
                  timeOut = Concat(timep1, ":", "30")
                }
                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
                  timeOut = Concat(timep1, ":", "45")
                }
                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
                  timeOut = Concat(timep1+1, ":", "00")
                }

                timeStamp = INA1[1].rawValue //INA1[1].formattedValue
                Cat = At(timeStamp,":")
                timep1 =  Left(timeStamp, Cat-1)
                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
                  timeIn = Concat(timep1, ":", "00")
                }
                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
                  timeIn = Concat(timep1, ":", "15")
                }
                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
                  timeIn = Concat(timep1, ":", "30")
                }
                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
                  timeIn = Concat(timep1, ":", "45")
                }
                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
                  timeIn = Concat(timep1+1, ":", "00")
                }

                LunchInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                }else {
                LunchInterval = 0
                }

                 

                var EndInterval

                if (HasValue(OUTA1[2]) and HasValue(INA1[2])) {
                timeStamp = OUTA1[2].rawValue //OUTA1[2].formattedValue
                Cat = At(timeStamp,":")
                timep1 =  Left(timeStamp, Cat-1)
                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
                  timeOut = Concat(timep1, ":", "00")
                }
                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
                  timeOut = Concat(timep1, ":", "15")
                }
                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
                  timeOut = Concat(timep1, ":", "30")
                }
                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
                  timeOut = Concat(timep1, ":", "45")
                }
                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
                  timeOut = Concat(timep1+1, ":", "00")
                }

                timeStamp = INA1[2].rawValue //INA1[2].formattedValue
                Cat = At(timeStamp,":")
                timep1 =  Left(timeStamp, Cat-1)
                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
                  timeIn = Concat(timep1, ":", "00")
                }
                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
                  timeIn = Concat(timep1, ":", "15")
                }
                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
                  timeIn = Concat(timep1, ":", "30")
                }
                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
                  timeIn = Concat(timep1, ":", "45")
                }
                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
                  timeIn = Concat(timep1+1, ":", "00")
                }

                EndInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                }else {
                EndInterval = 0
                }

                // compute total time in hours from the millisecond value
                Round(Sum(StartInterval, LunchInterval, EndInterval) / 3600000,2)

                 

                 

                may have to tweak little in this code, hope you can handle it form here.

                • 5. Re: TimeSheet to round to nearest quarter hour
                  derrick@work Community Member

                  Thanks a lot, this looks like what I need. But I'm new to FormCalc and this a little too advance for me. I tried plugging this into my timesheet and I get an error:

                   

                  Script failed (language is formcalc; contect is

                  xfa[0].form[0].topmostSubform[0].Page1[0].HW1[0])

                  script=...

                  Error: syntax error near token '{' on line 12, column 47.

                   

                  Could you explain the script a little more, sorry I'm really new to pdf scripting and just learning FormCalc. I really do appreciate your help with this, thank you very much.

                  • 6. Re: TimeSheet to round to nearest quarter hour
                    HarrisFR Community Member

                    Well this one I can actually answer, I'm fairly new to scripting wiht LCD myself.  I've done this several times myself.  Be sure to check that your scripting language is set to Formcalc and not Java.  If were using Java previously and forgot to change to Formcalc you will get that scripting error.  Easy mistake and very frustrating.

                    • 7. Re: TimeSheet to round to nearest quarter hour
                      Varma_LC Community Member

                      your attachment says queued....... I am not able to open it. Would you mind email your template to "n_varma AT lycos.com".

                      • 8. Re: TimeSheet to round to nearest quarter hour
                        derrick@work Community Member

                        It's set to FormCalc, thanks for the reply.

                        • 9. Re: TimeSheet to round to nearest quarter hour
                          derrick@work Community Member

                          You should be getting the e

                          • 10. Re: TimeSheet to round to nearest quarter hour
                            Varma_LC Community Member

                            Here you go....... It was silly mistake on my part....... messed up with syntax. FormCalc do not like "{, }"  instead we use "Then, Endif"

                             

                             

                            var timeStamp
                            var timeOut
                            var timeIn
                            var Cat
                            var timep1
                            var timep2

                            var StartInterval

                            if (HasValue($.parent.OUTA1[0]) and HasValue($.parent.INA1[0])) then
                            timeStamp = $.parent.OUTA1[0].rawValue //OUTA1[0].formattedValue
                            Cat = At(timeStamp,":")
                            timep1 =  Left(timeStamp, Cat-1)
                            timep2 = Right(timeStamp, len(timeStamp)- Cat)

                            if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                              timeOut = Concat(timep1, ":", "00")
                            endif
                            if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                              timeOut = Concat(timep1, ":", "15")
                            endif
                            if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                              timeOut = Concat(timep1, ":", "30")
                            endif
                            if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                              timeOut = Concat(timep1, ":", "45")
                            endif
                            if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                              timeOut = Concat(timep1+1, ":", "00")
                            endif

                            timeStamp = $.parent.INA1[0].rawValue //INA1[0].formattedValue
                            Cat = At(timeStamp,":")
                            timep1 =  Left(timeStamp, Cat-1)
                            timep2 = Right(timeStamp, len(timeStamp)- Cat)

                            if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                              timeIn = Concat(timep1, ":", "00")
                            endif
                            if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                              timeIn = Concat(timep1, ":", "15")
                            endif
                            if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                              timeIn = Concat(timep1, ":", "30")
                            endif
                            if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                              timeIn = Concat(timep1, ":", "45")
                            endif
                            if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                              timeIn = Concat(timep1+1, ":", "00")
                            endif

                            StartInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                            else
                            StartInterval = 0
                            endif

                            var LunchInterval

                            if (HasValue($.parent.OUTA1[1]) and HasValue($.parent.INA1[1])) then
                            timeStamp = $.parent.OUTA1[1].rawValue //OUTA1[1].formattedValue
                            Cat = At(timeStamp,":")
                            timep1 =  Left(timeStamp, Cat-1)
                            timep2 = Right(timeStamp, len(timeStamp)- Cat)

                            if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                              timeOut = Concat(timep1, ":", "00")
                            endif
                            if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                              timeOut = Concat(timep1, ":", "15")
                            endif
                            if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                              timeOut = Concat(timep1, ":", "30")
                            endif
                            if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                                timeOut = Concat(timep1, ":", "45")
                            endif
                            if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                              timeOut = Concat(timep1+1, ":", "00")
                            endif

                            timeStamp = $.parent.INA1[1].rawValue //INA1[1].formattedValue
                            Cat = At(timeStamp,":")
                            timep1 =  Left(timeStamp, Cat-1)
                            timep2 = Right(timeStamp, len(timeStamp)- Cat)

                            if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                              timeIn = Concat(timep1, ":", "00")
                            endif
                            if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                            timeIn = Concat(timep1, ":", "15")
                            endif
                            if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                              timeIn = Concat(timep1, ":", "30")
                            endif
                            if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                              timeIn = Concat(timep1, ":", "45")
                            endif
                            if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                              timeIn = Concat(timep1+1, ":", "00")
                            endif

                            LunchInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                            else
                            LunchInterval = 0
                            endif

                            var EndInterval

                            if (HasValue($.parent.OUTA1[2]) and HasValue($.parent.INA1[2])) then
                            timeStamp = $.parent.OUTA1[2].rawValue //OUTA1[2].formattedValue
                            Cat = At(timeStamp,":")
                            timep1 =  Left(timeStamp, Cat-1)
                            timep2 = Right(timeStamp, len(timeStamp)- Cat)

                            if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                              timeOut = Concat(timep1, ":", "00")
                            endif
                            if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                              timeOut = Concat(timep1, ":", "15")
                            endif
                            if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                              timeOut = Concat(timep1, ":", "30")
                            endif
                            if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                              timeOut = Concat(timep1, ":", "45")
                            endif
                            if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                              timeOut = Concat(timep1+1, ":", "00")
                            endif

                            timeStamp = $.parent.INA1[2].rawValue //INA1[2].formattedValue
                            Cat = At(timeStamp,":")
                            timep1 =  Left(timeStamp, Cat-1)
                            timep2 = Right(timeStamp, len(timeStamp)- Cat)

                            if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                              timeIn = Concat(timep1, ":", "00")
                            endif
                            if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                              timeIn = Concat(timep1, ":", "15")
                            endif
                            if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                              timeIn = Concat(timep1, ":", "30")
                            endif
                            if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                              timeIn = Concat(timep1, ":", "45")
                            endif
                            if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                            timeIn = Concat(timep1+1, ":", "00")
                            endif

                            EndInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                            else
                            EndInterval = 0
                            endif

                            // compute total time in hours from the millisecond value
                            Round(Sum(StartInterval, LunchInterval, EndInterval) / 3600000,2)

                            • 11. Re: TimeSheet to round to nearest quarter hour
                              derrick@work Community Member

                              It liked that one, but its not adding up right. If I put that I was in at 09:00 and out at 10:00 it shows 0 hours worked, but if I put in at 09:30 and out at 10:00 it shows -14.5 hours worked, and if i put in at 09:00 and out at 10:30 it shows 15.5 hours worked.

                              • 12. Re: TimeSheet to round to nearest quarter hour
                                Varma_LC Community Member

                                Improved again to fix other possible issues...........

                                 

                                var timeStamp
                                var timeOut
                                var timeIn
                                var Cat
                                var timep1
                                var timep2
                                var timep3
                                var timep4

                                var StartInterval

                                if (HasValue($.parent.OUTA1[0]) and HasValue($.parent.INA1[0])) then
                                timeStamp = $.parent.OUTA1[0].formattedValue
                                Cat = At(timeStamp,":")
                                timep1 =  Left(timeStamp, Cat-1)
                                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                                timeStamp = $.parent.INA1[0].formattedValue
                                Cat = At(timeStamp,":")
                                timep3 =  Left(timeStamp, Cat-1)
                                timep4 = Right(timeStamp, len(timeStamp)- Cat)

                                if (Ceil(timep1) < Ceil(timep3)) then
                                  timep1 = timep1+12
                                  if (timep1 <= 9) then
                                   timep1 = Concat("0", timep1)
                                   xfa.host.messageBox(timep1)
                                  endif
                                endif

                                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                                  timeOut = Concat(timep1, ":", "00")
                                endif
                                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                                  timeOut = Concat(timep1, ":", "15")
                                endif
                                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                                  timeOut = Concat(timep1, ":", "30")
                                endif
                                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                                  timeOut = Concat(timep1, ":", "45")
                                endif
                                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                                  timep1 = timep1+1
                                  if (timep1 <= 9) then
                                   timep1 = Concat("0", timep1)
                                //   xfa.host.messageBox(timep1)
                                  endif
                                  timeOut = Concat(timep1, ":", "00")
                                endif

                                if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
                                  timeIn = Concat(timep3, ":", "00")
                                endif
                                if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
                                  timeIn = Concat(timep3, ":", "15")
                                endif
                                if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
                                  timeIn = Concat(timep3, ":", "30")
                                endif
                                if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
                                  timeIn = Concat(timep3, ":", "45")
                                endif
                                if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
                                  timep3 = timep3+1
                                  if (timep3 <= 9) then
                                   timep3 = Concat("0", timep3)
                                //   xfa.host.messageBox(timep1)
                                  endif
                                  timeIn = Concat(timep3, ":", "00")
                                endif

                                StartInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                                else
                                StartInterval = 0
                                endif

                                var LunchInterval

                                if (HasValue($.parent.OUTA1[1]) and HasValue($.parent.INA1[1])) then
                                timeStamp = $.parent.OUTA1[1].formattedValue
                                Cat = At(timeStamp,":")
                                timep1 =  Left(timeStamp, Cat-1)
                                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                                timeStamp = $.parent.INA1[1].formattedValue
                                Cat = At(timeStamp,":")
                                timep3 =  Left(timeStamp, Cat-1)
                                timep4 = Right(timeStamp, len(timeStamp)- Cat)

                                if (Ceil(timep1) < Ceil(timep3)) then
                                  timep1 = timep1+12
                                endif

                                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                                  timeOut = Concat(timep1, ":", "00")
                                endif
                                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                                  timeOut = Concat(timep1, ":", "15")
                                endif
                                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                                  timeOut = Concat(timep1, ":", "30")
                                endif
                                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                                  timeOut = Concat(timep1, ":", "45")
                                endif
                                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                                  timep1 = timep1+1
                                  if (timep1 <= 9) then
                                   timep1 = Concat("0", timep1)
                                //   xfa.host.messageBox(timep1)
                                  endif
                                  timeOut = Concat(timep1, ":", "00")
                                endif

                                if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
                                  timeIn = Concat(timep3, ":", "00")
                                endif
                                if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
                                  timeIn = Concat(timep3, ":", "15")
                                endif
                                if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
                                  timeIn = Concat(timep3, ":", "30")
                                endif
                                if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
                                  timeIn = Concat(timep3, ":", "45")
                                endif
                                if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
                                  timep3 = timep3+1
                                  if (timep3 <= 9) then
                                   timep3 = Concat("0", timep3)
                                //   xfa.host.messageBox(timep1)
                                  endif
                                  timeIn = Concat(timep3, ":", "00")
                                endif

                                LunchInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                                else
                                LunchInterval = 0
                                endif

                                var EndInterval

                                if (HasValue($.parent.OUTA1[2]) and HasValue($.parent.INA1[2])) then
                                timeStamp = $.parent.OUTA1[2].formattedValue
                                Cat = At(timeStamp,":")
                                timep1 =  Left(timeStamp, Cat-1)
                                timep2 = Right(timeStamp, len(timeStamp)- Cat)

                                timeStamp = $.parent.INA1[2].formattedValue
                                Cat = At(timeStamp,":")
                                timep3 =  Left(timeStamp, Cat-1)
                                timep4 = Right(timeStamp, len(timeStamp)- Cat)

                                if (Ceil(timep1) < Ceil(timep3)) then
                                  timep1 = timep1+12
                                endif

                                if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
                                  timeOut = Concat(timep1, ":", "00")
                                endif
                                if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
                                  timeOut = Concat(timep1, ":", "15")
                                endif
                                if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
                                  timeOut = Concat(timep1, ":", "30")
                                endif
                                if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
                                  timeOut = Concat(timep1, ":", "45")
                                endif
                                if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
                                  timep1 = timep1+1
                                  if (timep1 <= 9) then
                                   timep1 = ""+Concat("0", timep1)
                                //   xfa.host.messageBox(timep1)
                                  endif
                                  timeOut = Concat(timep1, ":", "00")
                                endif

                                if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
                                  timeIn = Concat(timep3, ":", "00")
                                endif
                                if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
                                  timeIn = Concat(timep3, ":", "15")
                                endif
                                if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
                                  timeIn = Concat(timep3, ":", "30")
                                endif
                                if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
                                  timeIn = Concat(timep3, ":", "45")
                                endif
                                if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
                                  timep3 = timep3+1
                                  if (timep3 <= 9) then
                                   timep3 = Concat("0", timep3)
                                //   xfa.host.messageBox(timep1)
                                  endif
                                  timeIn = Concat(timep3, ":", "00")
                                endif

                                EndInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
                                else
                                EndInterval = 0
                                endif

                                // compute total time in hours from the millisecond value
                                Round(Sum(StartInterval, LunchInterval, EndInterval) / 3600000,2)

                                • 13. Re: TimeSheet to round to nearest quarter hour
                                  derrick@work Community Member

                                  This works perfectly. Thank you for your time, I hope that it wasn't much trouble. Now I have one last question and I just want to know if it's possible I can look into on to do it on my own. But is there a way to have it setup so that when they enter in the "Pay Period Beginning" and "Ending" date that it can fill out the "Date" column automatically? I hope I don't sound too needy, but it would be nice if they could spend less time filling out the timesheet. Thank you again LCProffesor I'm very greatful for your help.