Betreff: Need help rounding to the nearest quarter decimal in a working hours column on my timesheet form.

[nowackem]

Hi all,

Please see my form at the link below. I need help making my "Working Hours" column to round to the nearest quarter hour. Not sure the best way to go about this with all the other script in my form and could use some expertise.

https://drive.google.com/drive/folders/1PAJSki5-fdUXci0IYVkuEFvbCW5kzDwk?usp=sharing

Thank you!

Emilee

[radzmar]

Well, time calculations are often a headache. With this FormCalc script might be on the right way, I guess.

if (not timeIn.isNull and not timeOut.isNull) then
	var a = (Time2Num(timeIn.formattedValue)/ (60 * 60 * 1000))-(Time2Num(timeOut.formattedValue)/ (60 * 60 * 1000))
	var b = (Time2Num(lunchTimeOut.formattedValue)/ (60 * 60 * 1000))-(Time2Num(lunchTimeIn.formattedValue)/ (60 * 60 * 1000))
	var t = Format("z9.z88", b-a) ; format into number with 1 to 3 decimal places
	var s = Ltrim(Str(t, 10, 3)) ; turn into string and remove spaces on the left
	var p = At(s, ".") ; location of separator
	var i = Left(s, p) ; full hours
	var d = Right(s, p+1) ; decimal place
	
	; change decimal places depending on their current value
	if (Within(d, 0, 250) eq 1) then
		d = 250
	elseif (Within(d, 251, 500) eq 1) then
		d = 500
	elseif (Within(d, 501, 750) eq 1) then
		d = 750
	else
		d = 0
		i = Sum(i, 1)
	endif
	
	var r = Concat(i, d) ; combine to a result
	$ = r
endif

[nowackem]

@radzmar I've included a screenshot of what its doing, any thoughts on what I've missed?

[nowackem]

@radzmar below is something I found to represent how we need it to work as far as how the quarter hour calculations work. I've been playing with the solution but I'm having a hard time finalizing it and getting it to work for me. Any help is appreciated. Thanks again.

[nowackem]

Hello all! I cant find any information out there to help me out. Any input would be appreciated. The solution below by @radzmar is not quite there and I've tried to play with it but it seems with a little tweaking that it could be pretty awesome. I just need someone to help me take it over the hump.

I also wanted to make sure I expressed myself correctly. The user should be able to enter the exact times they come and go but when the Working Hours calculate, that's when I need it to round to the nearest 15 min interval. Thank you so much! 

[nowackem · Accepted solution]

Good Morning, I have been able to make this work adapting code in another solution from 2009 but the only way was using military time. If anyone has a way to let the user enter either 12 or 24-hour time in the Time In and Out fields and still get the calculations feel free to let me know.  The form is located here (https://drive.google.com/drive/folders/1l8pcqcTVBdvNlEkiK5AdMuTHnsMk9I1J?usp=sharing) if this helps anyone and the script I used is below:

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

var StartInterval

if (HasValue($.parent.timeClockOut) and HasValue($.parent.timeClockIn)) then
timeStamp = $.parent.timeClockOut.formattedValue
Cat = At(timeStamp,":")
timep1 = Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = $.parent.timeClockIn.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)
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)
endif
timeIn = concat(timep3, ":", "00")
endif

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

var LunchInterval

if (HasValue($.parent.lunchTimeClockIn) and HasValue($.parent.lunchTimeClockOut)) then
timeStamp = $.parent.lunchTimeClockIn.formattedValue
Cat = At(timeStamp,":")
timep1 = Left(timeStamp, Cat-1)
timep2 = Right(timeStamp, len(timeStamp)- Cat)

timeStamp = $.parent.lunchTimeClockOut.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)
endif
timeIn = concat(timep3, ":", "00")
endif

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

// compute total time in hours from the millisecond value
sum((StartInterval - LunchInterval) / 3600000)