Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

TimeSheet to Be Round to the Nearest Quarter Hour

Avatar

Avatar
Level 1
stacied19291882
Level 1

Likes

0 likes

Total Posts

2 posts

Correct Reply

0 solutions
View profile

Avatar
Level 1
stacied19291882
Level 1

Likes

0 likes

Total Posts

2 posts

Correct Reply

0 solutions
View profile
stacied19291882
Level 1

30-06-2015

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)

View Entire Topic

Avatar

Avatar
Validate 1
Level 4
jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

Correct Reply

43 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Validate 1
Level 4
jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

Correct Reply

43 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile
jasotastic81
Level 4

02-07-2015

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?