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.
George Kaiser