Need help rounding to the nearest quarter decimal in a working hours column on my timesheet form. | Adobe Higher Education
Skip to main content
nowackem
nowackem
Level 5
May 3, 2022
Resuelto

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

  • May 3, 2022
  • 3 respuestas
  • 1447 visualizaciones

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

Este tema ha sido cerrado para respuestas.
Mejor respuesta de nowackem

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)

 

3 respuestas

radzmar
radzmar
Level 10
May 4, 2022

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
    nowackemAutor
    Level 5
    May 4, 2022

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

     

     

      nowackem
      nowackemAutor
      Level 5
      May 10, 2022

      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
      nowackemAutorRespuesta
      Level 5
      May 11, 2022

      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)

       

      Términos y condicionesAccessibility statement

      Loading footer...