TimeSheet to Be Round to the Nearest Quarter Hour

[Former Community Member]

I have a very simple time sheet that I need the total hours worked rounded to the nearest quarter of an hour.

An employee that arrives between 8 and 8:07 needs to be 8:00

An employee that arrives between 8:08 and 8:22 needs to be 8:15

An employee that arrives between 8:23 and to 8:37 needs to be 8:30

An employee that arrives between 8:38 and 8:52 needs to be 8:45

An employee that arrives between 8:53 and 9 needs to be 9:00.

I copied a FormCalc from one discussion but it is not working

var timeStamp

var timeOut

var timeIn

var Cat

var timep1

var timep2

var timep3

var timep4

var StartInterval

if (HasValue($.parent.TIME_OUT89) and HasValue($.parent.TIME_IN89)) then

timeStamp = $.parent.TIME_OUT89.formattedValue

Cat = At(timeStamp,":")

timep1 =  Left(timeStamp, Cat-1)

timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = $.parent.TIME_IN89.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)

  endif

if (Ceil(timep2) >= 0 and Ceil(timep2) <= 7) then

  timeOut = Concat(timep1, ":", "00")

if (Ceil(timep2) >= 8 and Ceil(timep2) <= 22) then

  timeOut = Concat(timep1, ":", "15")

if (Ceil(timep2) >= 23 and Ceil(timep2) <= 37) then

  timeOut = Concat(timep1, ":", "30")

if (Ceil(timep2) >= 38 and Ceil(timep2) <= 52) then

  timeOut = Concat(timep1, ":", "45")

if (Ceil(timep2) >= 53 and Ceil(timep2) <= 59) then

  timep1 = timep1+1

  if (timep1 <= 9) then

  timep1 = Concat("0", timep1)

  endif

  timeOut = Concat(timep1, ":", "00")

if (Ceil(timep4) >= 0 and Ceil(timep4) <= 7) then

  timeIn = Concat(timep3, ":", "00")

if (Ceil(timep4) >= 8 and Ceil(timep4) <= 22) then

  timeIn = Concat(timep3, ":", "15")

if (Ceil(timep4) >= 23 and Ceil(timep4) <= 37) then

  timeIn = Concat(timep3, ":", "30")

if (Ceil(timep4) >= 38 and Ceil(timep4) <= 52) then

  timeIn = Concat(timep3, ":", "45")

if (Ceil(timep4) >= 53 and Ceil(timep4) <= 59) then

  timep3 = timep3+1

  if (timep3 <= 9) then

  timep3 = Concat("0", timep3)

  endif

  timeIn = Concat(timep3, ":", "00")

StartInterval = Abs(Time2Num(TIME_OUT89, "HH:MM") - Time2Num(TIME_IN89, "HH:MM"))

StartInterval = 0

Round(Sum(StartInterval)/3600000,2)

[jasotastic81]

The first thing I notice is that there are a LOT of endif's missing. From the FormCalc reference: You are not required to have any elseif(...) or else statements as part of your if expression, but you must state the end of the expression with endif. (Emphasis mine.)

Also, towards the end (and this might be due to the lack of endif's) your function calculated the value of StartInterval, then sets it to 0. So, even if the rest of the function was working, that last bit would guarantee that the answer is always 0.

Next, this code seems to be calculating numbers from a table. Do you have your table set up the same way with the same field/object names? Or have you modified this script to match what you have?

Finally, even if the code issues were corrected, this doesn't seem to do what you asked for. From your requirements, I gather that you want the information the user entered to be rounded and copied somewhere else on the form, but this code calculates hours worked, instead. Are you wanting the user's entries to be copied somewhere else and rounded so you can calculate from the rounded numbers? Or are you wanting the hours calculated first, then rounded to the nearest quarter hour?

[Former Community Member]

I actually started to google my question and stumbled on adobe communities.  I saw someone had asked what I thought was the same question a few years ago.  I copied what they did and I hoped it would work.  I know a little about FormCalc but not a lot.  I want the employee to be able to enter their time in and their time out and then the formula round it to the nearest quarter hour.  Currently our time system uses the 7 and 8 minute rule that I stated above.  I tried to attach my timesheet so you could see a visual but it would not allow me.