These forums are now Read Only. If you have an Acrobat question, ask questions and get help from one of our experts.

Time Sheet to round to the nearest quarter hour

derrick_work
Registered: Jun 2 2009
Posts: 4

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
Sum(StartInterval, LunchInterval, EndInterval) / 3600000

I know in excel I can use the equation =(ROUND(B2*96, 0)/96)-(ROUND(A2*96, 0)/96) to get the result I want. I've tried plugging something similar into FormCalc but was not able to get it to work. If you could tell me what I'm doing wrong I'd be greatly appreciative.

My Product Information:
LiveCycle Designer, Windows
gkaiseril
Online
Expert
Registered: Feb 23 2006
Posts: 4308
Have you looked at [url:http://www.acrobatusers.com/forums/aucbb/viewtopic.php?id=20348]converting time entries to whole numbers and decimals[/url] that discussed this same problem and has a function that can round to the nearest of any selected value. Even though the code is Acrobat JavaScript you should be able to modify the code to comply with LiveCycle Designer FormCalc or JavaScript.

George Kaiser

derrick_work
Registered: Jun 2 2009
Posts: 4
Thanks for the reply, I haven't seen this yet. But I'm going to need some further explanation. I'm very new with FormCalc and don't know how to do anything with JavaScript. I will look through this and see how far I can get. If you could give me a few more hints on how this would work, that would be great. Thanks again