9 Replies Latest reply on Jul 24, 2013 11:29 AM by LauraRussell

    Calculations Between Two Time Fields

    Jo_2005 Level 1

      Hello,

       

      I have a form with a Time Field for the start time, and a text field for the user to enter a duration (eg 00:30:00).

       

      There is another field for the End Time (which will have a calculation script using the start time and duration fields).

       

      The script which has been placed in the End Time Field works except the script is giving a negative result.

       

      Eg start time field is 16:03:33  and duration field is 00:30:00  Calculation for End Time Field is showing as -16:-33

       

      Can someone please advise how I can revise the script for the End Time Field to show the correct result of 16:33 (without the negative)?


      // create string for today's date
      var oToday = new Date();
      var sToday = util.printd("yyyy/mm/dd ", oToday);

      // get value of start time field

      var sStart = this.getField("Action Start Time").value;

      // create JavaScript date time object for start time
      var oStart = util.scand("yyyy/mm/dd HH:MM:ss", sToday + sStart);


      // get value of duration

      var sEnd = this.getField("Action Time").value;

      // create JavaScript date time object for end time
      var oEnd = util.scand("yyyy/mm/dd hh:mm", sToday + sEnd);

       

      // compute difference in hours from the difference in hours
      var DiffHours = (oEnd.valueOf() - oStart.valueOf()) / 1000 / 60 / 60;
      event.value = Math.floor(DiffHours)+ ":" + util.printf("%,302.0f", (((DiffHours % 1) * 60).toFixed()));

       

       

      Any assistance will be greatly appreciated.

       

        • 1. Re: Calculations Between Two Time Fields
          try67 MVP & Adobe Community Professional

          Why are you using different formats for oStart and oEnd ?

          1 person found this helpful
          • 2. Re: Calculations Between Two Time Fields
            Jo_2005 Level 1

            Good observation, an error on my behalf.  I have revised the script to be the same format for both variants but still get the negative result.

             

            Can you please advise where the script can be modified to give the correct result.

             

            Modified script as follows:

             

            // create string for today's date
            var oToday = new Date();
            var sToday = util.printd("yyyy/mm/dd ", oToday);

            // get value of start time

            var sStart = this.getField("Action Start Time").value;

            // create JavaScript date time object for start time
            var oStart = util.scand("yyyy/mm/dd HH:MM:ss", sToday + sStart);


            // get value of end time
            var sEnd = this.getField("Action Time").value;

            // create JavaScript date time object for end time
            var oEnd = util.scand("yyyy/mm/dd HH:MM:ss", sToday + sEnd);

             

            // compute difference in hours from the difference in hours
            var DiffHours = (oEnd.valueOf() - oStart.valueOf()) / 1000 / 60 / 60;
            event.value = Math.floor(DiffHours)+ ":" + util.printf("%,302.0f", (((DiffHours % 1) * 60).toFixed()));

             

            Appreciate your help.

             

            Best Regards

            Jo

             

            • 3. Re: Calculations Between Two Time Fields
              try67 MVP & Adobe Community Professional

              I tried to understand the script, but I think your approach to this is

              wrong.

              For example, if you enter the duration to be "00:30:05", the result is

              "-16:-60", so the problem is not just the fact that it's a negative number.

              I would suggest re-thinking how you do it.

              • 4. Re: Calculations Between Two Time Fields
                Jo_2005 Level 1

                Can you please advise another approach to this, to get me started  in the right direction??

                 

                thank you very much.

                 

                Jo

                 

                • 5. Re: Calculations Between Two Time Fields
                  gkaiseril MVP & Adobe Community Professional

                  Have you read your comments in the code?

                   

                  The comment that reads "// compute difference in hours from the difference in hours" indicates the code is computing the difference between your field "Action Start Time" and your field "Action Time" by subtracting the 'Action Time" from the "Action Start Time". This might account for your gettng the negative value.

                   

                  I would guess you want to 'add' the "Acrtion Time" value to the "Action Start Time" value.

                  • 6. Re: Calculations Between Two Time Fields
                    Jo_2005 Level 1

                    Thank you for your reply.

                     

                    I have added the "Acrion Time" value to the "Action Start Time" value with the following result:

                     

                    Start Time Field:  08:30:35

                     

                    Action Time Field: (user input for the duration)  00:30:00

                     

                    End Time Field: (this is where the calculation script is)  result:  717347:01

                     

                    Can you please advise how I can modify the script to calculate the correct time?

                     

                    Your advice will be most appreciated.

                     

                    Best Regards

                    Jo

                     

                    • 7. Re: Calculations Between Two Time Fields
                      gkaiseril MVP & Adobe Community Professional

                      You can not just change the '-' operator to the '+' operator because of modulus computational nature of the date and time calculations. You need to compute the various parts of the date and time from the start time and elapsed time and then convert those individual values back to a date time object and format that new date time object.

                       

                      // clear the field
                      event.value = "";
                      // get the activity start time
                      var sEventStart = this.getField("Activity Start Time").value;
                      // compute only if there is an start time
                      if (sEventStart != "") {
                      // console operations are for debugging and illustration
                      /*
                      console.show();
                      console.clear();
                      */
                      // convert to a date object change format as needed
                      var oEventStart = util.scand("yyyy, mm, dd, HH:MM:ss", nNowYear + ", " + (nNowMonth + 1) + ", " + nNowDate + ", " + sEventStart);
                      // console.println('oEventStart: ' + oEventStart);

                      // get current date time
                      var oNow = new Date();
                      // console.println('oNow: ' + oNow);
                      // get parts of the date
                      var nNowYear = oNow.getFullYear();
                      var nNowMonth = oNow.getMonth();
                      var nNowDate = oNow.getDate();
                      /*
                      console.println('nNowYear: ' + nNowYear);
                      console.println('nNowMonth: ' + nNowMonth);
                      console.println('nNowDate: ' + nNowDate);
                      */
                      // get the activity duration
                      var sEventDuration = this.getField('Event Duration').value;
                      // if no entry force to zero time
                      if(sEventDuration == '') sEventDuration = "00:00:00";
                      // convert duration into hours and minutes
                      var aEventDuration = sEventDuration.split(":");
                      /*
                      console.println('Hours - aEventDuration[0]: ' + aEventDuration[0]);
                      console.println('Minutes - aEventDuration[1]: ' + aEventDuration[1]);
                      console.println('Start hours: ' + oEventStart.getHours());
                      console.println('Start minutes: ' + oEventStart.getMinutes());
                      console.println('Start seconds: ' + oEventStart.getSeconds());
                      */
                      // compute end hour and minutes
                      var nEndHour = Number(oEventStart.getHours()) + Number(aEventDuration[0]);
                      var nEndMinute = Number(oEventStart.getMinutes()) + Number(aEventDuration[1]);
                      if(aEventDuration.length == 3)
                      var nEndSecond = Number(oEventStart.getSeconds()) + Number(aEventDuration[2]);
                      else
                      var nEndSecond = Number(oEventStart.getSeconds()) + 0;
                      /*
                      console.println('nEndHour: ' + nEndHour);
                      console.println('nEndMinute: ' + nEndMinute);
                      console.println('nEndSecond: ' + nEndSecond);
                      console.println('nNowYear: ' + nNowYear);
                      console.println('nNowMonth: ' + nNowMonth);
                      console.println('nNowDate: ' + nNowDate);
                      */
                      // make date object for event end - calculate hours and minutes
                      var oEnd = new Date(nNowYear, nNowMonth, nNowDate, nEndHour, nEndMinute, nEndSecond);
                      // console.println('oEnd: ' + oEnd);
                      // change format as needed
                      event.value = util.printd("HH:MM:ss", oEnd);
                      } // end compute event time

                      • 8. Re: Calculations Between Two Time Fields
                        Jo_2005 Level 1

                        Thank you so much for the calculations between two time fields -  With a Start Time and a Duration to get an End Time.

                         

                        The script was placed in the text field for "End Time" and now correctly calculates the finish time.

                         

                        I am most grateful for your assistance.

                         

                         

                        • 9. Re: Calculations Between Two Time Fields
                          LauraRussell

                          This may be a dead thread, but I thought I would try it.

                           

                          I have three rows that I am trying to calculate the time:

                           

                          TimeSpent = Time Spent (which will be the calculation for the other two fields)

                          TimeIn - Time In (what time the client arrives)

                          TimeOut - Time Out (what time the client leaves)

                           

                          I have tried to insert the above script to see if it would help me to be able to calculate the "Time In" + "Time Out" to give me a total time "TimeSpent" that the client was in our office....but I have not had any luck...I tried to replace the code in the above script, but I have no clue as to what I am doing with script and do not understand it whatsoever.

                           

                          What would I need to change in the above script in order to have my three fields work together to give me the time spent?

                           

                          Thank you in advance for your help!