Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

## TimeSheet to round to nearest quarter hour

Level 2

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

1 Accepted Solution

Level 6

Improved again to fix other possible issues...........

var timeStamp
var timeOut
var timeIn
var Cat
var timep1
var timep2
var timep3
var timep4

var StartInterval

if (HasValue(\$.parent.OUTA1[0]) and HasValue(\$.parent.INA1[0])) then
timeStamp = \$.parent.OUTA1[0].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = \$.parent.INA1[0].formattedValue
Cat = At(timeStamp,":")
timep3 =  Left(timeStamp, Cat-1)
timep4 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep1) < Ceil(timep3)) then
timep1 = timep1+12
if (timep1 <= 9) then
timep1 = Concat("0", timep1)
xfa.host.messageBox(timep1)
endif
endif

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timep1 = timep1+1
if (timep1 <= 9) then
timep1 = Concat("0", timep1)
//   xfa.host.messageBox(timep1)
endif
timeOut = Concat(timep1, ":", "00")
endif

if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
timeIn = Concat(timep3, ":", "00")
endif
if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
timeIn = Concat(timep3, ":", "15")
endif
if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
timeIn = Concat(timep3, ":", "30")
endif
if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
timeIn = Concat(timep3, ":", "45")
endif
if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
timep3 = timep3+1
if (timep3 <= 9) then
timep3 = Concat("0", timep3)
//   xfa.host.messageBox(timep1)
endif
timeIn = Concat(timep3, ":", "00")
endif

StartInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
StartInterval = 0
endif

var LunchInterval

if (HasValue(\$.parent.OUTA1[1]) and HasValue(\$.parent.INA1[1])) then
timeStamp = \$.parent.OUTA1[1].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = \$.parent.INA1[1].formattedValue
Cat = At(timeStamp,":")
timep3 =  Left(timeStamp, Cat-1)
timep4 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep1) < Ceil(timep3)) then
timep1 = timep1+12
endif

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timep1 = timep1+1
if (timep1 <= 9) then
timep1 = Concat("0", timep1)
//   xfa.host.messageBox(timep1)
endif
timeOut = Concat(timep1, ":", "00")
endif

if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
timeIn = Concat(timep3, ":", "00")
endif
if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
timeIn = Concat(timep3, ":", "15")
endif
if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
timeIn = Concat(timep3, ":", "30")
endif
if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
timeIn = Concat(timep3, ":", "45")
endif
if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
timep3 = timep3+1
if (timep3 <= 9) then
timep3 = Concat("0", timep3)
//   xfa.host.messageBox(timep1)
endif
timeIn = Concat(timep3, ":", "00")
endif

LunchInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
LunchInterval = 0
endif

var EndInterval

if (HasValue(\$.parent.OUTA1[2]) and HasValue(\$.parent.INA1[2])) then
timeStamp = \$.parent.OUTA1[2].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = \$.parent.INA1[2].formattedValue
Cat = At(timeStamp,":")
timep3 =  Left(timeStamp, Cat-1)
timep4 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep1) < Ceil(timep3)) then
timep1 = timep1+12
endif

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timep1 = timep1+1
if (timep1 <= 9) then
timep1 = ""+Concat("0", timep1)
//   xfa.host.messageBox(timep1)
endif
timeOut = Concat(timep1, ":", "00")
endif

if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
timeIn = Concat(timep3, ":", "00")
endif
if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
timeIn = Concat(timep3, ":", "15")
endif
if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
timeIn = Concat(timep3, ":", "30")
endif
if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
timeIn = Concat(timep3, ":", "45")
endif
if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
timep3 = timep3+1
if (timep3 <= 9) then
timep3 = Concat("0", timep3)
//   xfa.host.messageBox(timep1)
endif
timeIn = Concat(timep3, ":", "00")
endif

EndInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
EndInterval = 0
endif

// compute total time in hours from the millisecond value
Round(Sum(StartInterval, LunchInterval, EndInterval) / 3600000,2)

13 Replies

Level 2

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

Level 7

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.

Level 2

Thanks for the reply, any idea on how to do quarter hour rounding in FormCalc than?

Level 6

I would change your FormCalc script to look like this..........

var timeStamp
var timeOut
var timeIn
var Cat
var timep1
var timep2

var StartInterval

if (HasValue(OUTA1[0]) and HasValue(INA1[0])) {
timeStamp = OUTA1[0].rawValue //OUTA1[0].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
timeOut = Concat(timep1, ":", "00")
}
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
timeOut = Concat(timep1, ":", "15")
}
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
timeOut = Concat(timep1, ":", "30")
}
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
timeOut = Concat(timep1, ":", "45")
}
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
timeOut = Concat(timep1+1, ":", "00")
}

timeStamp = INA1[0].rawValue //INA1[0].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
timeIn = Concat(timep1, ":", "00")
}
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
timeIn = Concat(timep1, ":", "15")
}
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
timeIn = Concat(timep1, ":", "30")
}
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
timeIn = Concat(timep1, ":", "45")
}
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
timeIn = Concat(timep1+1, ":", "00")
}

StartInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
}else {
StartInterval = 0
}

var LunchInterval

if (HasValue(OUTA1[1]) and HasValue(INA1[1])) {
timeStamp = OUTA1[1].rawValue //OUTA1[1].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
timeOut = Concat(timep1, ":", "00")
}
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
timeOut = Concat(timep1, ":", "15")
}
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
timeOut = Concat(timep1, ":", "30")
}
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
timeOut = Concat(timep1, ":", "45")
}
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
timeOut = Concat(timep1+1, ":", "00")
}

timeStamp = INA1[1].rawValue //INA1[1].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
timeIn = Concat(timep1, ":", "00")
}
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
timeIn = Concat(timep1, ":", "15")
}
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
timeIn = Concat(timep1, ":", "30")
}
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
timeIn = Concat(timep1, ":", "45")
}
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
timeIn = Concat(timep1+1, ":", "00")
}

LunchInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
}else {
LunchInterval = 0
}

var EndInterval

if (HasValue(OUTA1[2]) and HasValue(INA1[2])) {
timeStamp = OUTA1[2].rawValue //OUTA1[2].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
timeOut = Concat(timep1, ":", "00")
}
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
timeOut = Concat(timep1, ":", "15")
}
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
timeOut = Concat(timep1, ":", "30")
}
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
timeOut = Concat(timep1, ":", "45")
}
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
timeOut = Concat(timep1+1, ":", "00")
}

timeStamp = INA1[2].rawValue //INA1[2].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) {
timeIn = Concat(timep1, ":", "00")
}
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) {
timeIn = Concat(timep1, ":", "15")
}
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) {
timeIn = Concat(timep1, ":", "30")
}
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) {
timeIn = Concat(timep1, ":", "45")
}
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) {
timeIn = Concat(timep1+1, ":", "00")
}

EndInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
}else {
EndInterval = 0
}

// compute total time in hours from the millisecond value
Round(Sum(StartInterval, LunchInterval, EndInterval) / 3600000,2)

may have to tweak little in this code, hope you can handle it form here.

Level 2

Thanks a lot, this looks like what I need. But I'm new to FormCalc and this a little too advance for me. I tried plugging this into my timesheet and I get an error:

Script failed (language is formcalc; contect is

xfa[0].form[0].topmostSubform[0].Page1[0].HW1[0])

script=...

Error: syntax error near token '{' on line 12, column 47.

Could you explain the script a little more, sorry I'm really new to pdf scripting and just learning FormCalc. I really do appreciate your help with this, thank you very much.

Level 3

Well this one I can actually answer, I'm fairly new to scripting wiht LCD myself.  I've done this several times myself.  Be sure to check that your scripting language is set to Formcalc and not Java.  If were using Java previously and forgot to change to Formcalc you will get that scripting error.  Easy mistake and very frustrating.

Level 6

your attachment says queued....... I am not able to open it. Would you mind email your template to "n_varma AT lycos.com".

Level 2

It's set to FormCalc, thanks for the reply.

Level 2

You should be getting the e

Level 6

Here you go....... It was silly mistake on my part....... messed up with syntax. FormCalc do not like "{, }"  instead we use "Then, Endif"

var timeStamp
var timeOut
var timeIn
var Cat
var timep1
var timep2

var StartInterval

if (HasValue(\$.parent.OUTA1[0]) and HasValue(\$.parent.INA1[0])) then
timeStamp = \$.parent.OUTA1[0].rawValue //OUTA1[0].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timeOut = Concat(timep1+1, ":", "00")
endif

timeStamp = \$.parent.INA1[0].rawValue //INA1[0].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeIn = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeIn = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeIn = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeIn = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timeIn = Concat(timep1+1, ":", "00")
endif

StartInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
StartInterval = 0
endif

var LunchInterval

if (HasValue(\$.parent.OUTA1[1]) and HasValue(\$.parent.INA1[1])) then
timeStamp = \$.parent.OUTA1[1].rawValue //OUTA1[1].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timeOut = Concat(timep1+1, ":", "00")
endif

timeStamp = \$.parent.INA1[1].rawValue //INA1[1].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeIn = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeIn = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeIn = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeIn = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timeIn = Concat(timep1+1, ":", "00")
endif

LunchInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
LunchInterval = 0
endif

var EndInterval

if (HasValue(\$.parent.OUTA1[2]) and HasValue(\$.parent.INA1[2])) then
timeStamp = \$.parent.OUTA1[2].rawValue //OUTA1[2].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timeOut = Concat(timep1+1, ":", "00")
endif

timeStamp = \$.parent.INA1[2].rawValue //INA1[2].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeIn = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeIn = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeIn = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeIn = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timeIn = Concat(timep1+1, ":", "00")
endif

EndInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
EndInterval = 0
endif

// compute total time in hours from the millisecond value
Round(Sum(StartInterval, LunchInterval, EndInterval) / 3600000,2)

Level 2

It liked that one, but its not adding up right. If I put that I was in at 09:00 and out at 10:00 it shows 0 hours worked, but if I put in at 09:30 and out at 10:00 it shows -14.5 hours worked, and if i put in at 09:00 and out at 10:30 it shows 15.5 hours worked.

Level 6

Improved again to fix other possible issues...........

var timeStamp
var timeOut
var timeIn
var Cat
var timep1
var timep2
var timep3
var timep4

var StartInterval

if (HasValue(\$.parent.OUTA1[0]) and HasValue(\$.parent.INA1[0])) then
timeStamp = \$.parent.OUTA1[0].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = \$.parent.INA1[0].formattedValue
Cat = At(timeStamp,":")
timep3 =  Left(timeStamp, Cat-1)
timep4 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep1) < Ceil(timep3)) then
timep1 = timep1+12
if (timep1 <= 9) then
timep1 = Concat("0", timep1)
xfa.host.messageBox(timep1)
endif
endif

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timep1 = timep1+1
if (timep1 <= 9) then
timep1 = Concat("0", timep1)
//   xfa.host.messageBox(timep1)
endif
timeOut = Concat(timep1, ":", "00")
endif

if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
timeIn = Concat(timep3, ":", "00")
endif
if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
timeIn = Concat(timep3, ":", "15")
endif
if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
timeIn = Concat(timep3, ":", "30")
endif
if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
timeIn = Concat(timep3, ":", "45")
endif
if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
timep3 = timep3+1
if (timep3 <= 9) then
timep3 = Concat("0", timep3)
//   xfa.host.messageBox(timep1)
endif
timeIn = Concat(timep3, ":", "00")
endif

StartInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
StartInterval = 0
endif

var LunchInterval

if (HasValue(\$.parent.OUTA1[1]) and HasValue(\$.parent.INA1[1])) then
timeStamp = \$.parent.OUTA1[1].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = \$.parent.INA1[1].formattedValue
Cat = At(timeStamp,":")
timep3 =  Left(timeStamp, Cat-1)
timep4 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep1) < Ceil(timep3)) then
timep1 = timep1+12
endif

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timep1 = timep1+1
if (timep1 <= 9) then
timep1 = Concat("0", timep1)
//   xfa.host.messageBox(timep1)
endif
timeOut = Concat(timep1, ":", "00")
endif

if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
timeIn = Concat(timep3, ":", "00")
endif
if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
timeIn = Concat(timep3, ":", "15")
endif
if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
timeIn = Concat(timep3, ":", "30")
endif
if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
timeIn = Concat(timep3, ":", "45")
endif
if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
timep3 = timep3+1
if (timep3 <= 9) then
timep3 = Concat("0", timep3)
//   xfa.host.messageBox(timep1)
endif
timeIn = Concat(timep3, ":", "00")
endif

LunchInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
LunchInterval = 0
endif

var EndInterval

if (HasValue(\$.parent.OUTA1[2]) and HasValue(\$.parent.INA1[2])) then
timeStamp = \$.parent.OUTA1[2].formattedValue
Cat = At(timeStamp,":")
timep1 =  Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = \$.parent.INA1[2].formattedValue
Cat = At(timeStamp,":")
timep3 =  Left(timeStamp, Cat-1)
timep4 = Right(timeStamp, len(timeStamp)- Cat)

if (Ceil(timep1) < Ceil(timep3)) then
timep1 = timep1+12
endif

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then
timeOut = Concat(timep1, ":", "00")
endif
if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then
timeOut = Concat(timep1, ":", "15")
endif
if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then
timeOut = Concat(timep1, ":", "30")
endif
if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then
timeOut = Concat(timep1, ":", "45")
endif
if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then
timep1 = timep1+1
if (timep1 <= 9) then
timep1 = ""+Concat("0", timep1)
//   xfa.host.messageBox(timep1)
endif
timeOut = Concat(timep1, ":", "00")
endif

if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then
timeIn = Concat(timep3, ":", "00")
endif
if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then
timeIn = Concat(timep3, ":", "15")
endif
if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then
timeIn = Concat(timep3, ":", "30")
endif
if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then
timeIn = Concat(timep3, ":", "45")
endif
if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then
timep3 = timep3+1
if (timep3 <= 9) then
timep3 = Concat("0", timep3)
//   xfa.host.messageBox(timep1)
endif
timeIn = Concat(timep3, ":", "00")
endif

EndInterval = Time2Num(timeOut, "HH:MM") - Time2Num(timeIn, "HH:MM")
else
EndInterval = 0
endif

// compute total time in hours from the millisecond value
Round(Sum(StartInterval, LunchInterval, EndInterval) / 3600000,2)

Level 2

This works perfectly. Thank you for your time, I hope that it wasn't much trouble. Now I have one last question and I just want to know if it's possible I can look into on to do it on my own. But is there a way to have it setup so that when they enter in the "Pay Period Beginning" and "Ending" date that it can fill out the "Date" column automatically? I hope I don't sound too needy, but it would be nice if they could spend less time filling out the timesheet. Thank you again LCProffesor I'm very greatful for your help.