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)