TimeSheet to round to nearest quarter hour

[derrick1]

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

[Former Community Member]

For lack of a more creative or elegant solution, brute force (read 'code') always works for me. The code is based upon a 24 hour time format (HH::MM).

// form1.page1.subform1.time::exit - (JavaScript, client)

var time_ = this.rawValue;

var hour_ = time_.substring(0,2);

var min_ = time_.substring(2,4);

var minStr = "";

if (min_ > 7 && min_ < 23) {

     minStr = "15";

}

else {

     if (min_ > 22 && min_ < 38) {

          minStr = "30";

     }

     else {

          if (min_ > 37 && min_ < 53) {

               minStr = "45";

          }

          else {

               minStr = "00";

               if (min_ > 52) {

                    hour_++;

               }

          }

     }

}

form1.page1.subform1.clockTime.rawValue = hour_.toString() + ":" + minStr;

Steve

[derrick1]

Thank you for your reply Steve, but please forgive me. I have no experience with JavaScript or FormCalc for that matter, and I'm learning from the ground up. So I don't know where to begin to implement the solution you have shown. Would you be able to explain in detail how this works? I'm sorry for my lack of experience with pdfs. Again thanks for your reply, Derrick.

[Former Community Member]

No need to apologize. This forum is about knowledge acquisiton.

Do you wish to display the rounded number in each time field?

or

Do you want the time entered by the user to display as entered and the rounded value used in the hours worked calculation?

Steve

[derrick1]

Thanks Steve, I was looking for a way so that it will show the actual times they start and end, but round in the hours work column to the nearest quarter hour, if that's possible?

[derrick1]

Hey Steve, 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

[Former Community Member]

The is Acroform JavaScript rather than LiveCycle Designer ES JavaScript. The object reference sytax 'this.getField()' and the function 'util' are from Acrobat.

The script is using 'util.scand' and 'util.printd' to convert Acroform date objects/formats into JavaScript date objects. The JavaScript date objects represent milliseconds since the Epoch (I believe the Epoch in this case is Jan. 1, 1970). The calculations that follow convert the milliseconds to hours and minutes, do rounding, and compute the difference between the start and end time.

The script will not work for you. The custom function ('Round2Nearest') contains the magic you seek.