TimeSheet to round to nearest quarter hour

Avatar

Avatar

derrick1

Avatar

derrick1

derrick1

16-06-2009

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

View Entire Topic

Avatar

Avatar

gkaiseril

Avatar

gkaiseril

gkaiseril

16-06-2009

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.