Forum Discussion

JustinPauga's avatar
JustinPauga
Qrew Cadet
8 years ago

Clock in/out round to the nearest quarter hour

I have added time card functionality in my application using the Essentials Employee Clock In/Clock Out app as a reference. I have the time cards working really well but I was wondering if there was a way to round the time in/out to the nearest quarter hour? I don't want our payroll person to have to deal with a clock in of 8:23am. Instead I would like 8:23am to get rounded up to 8:30am with 8:22am being rounded down to 8:15am
App builders
Ask about Quickbase functionality
Formulas and functions
  • MelissaDoran's avatar
    MelissaDoran
    Qrew Cadet
    8 years ago
    You could have a field where the employee's entry is recorded, then you could have a second field that is rounded.
    Maybe something like
    Round(ToNumber(EmpTime),.25)
    You'd have to turn it back into a time
    Idk if you can round times w/o turning it to a number?

    There's prob a cleaner solution but at least that's something.
    And no i didn't test it, it's 1 AM and I need to go home.
    Let me know how it goes though :)
    • JustinPauga's avatar
      JustinPauga
      Qrew Cadet
      8 years ago
      I've tried that but unfortunately the time in and out uses the Now() function which returns a date with the time so the ToNumber just gives me the number of the month. I appreciate the help though and it led me to some good resources for future formulas I will use but I still have the same issue regarding my timecard formula
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Try this

    var duration RoundedDurationFromMidnight =

    Round([My Time Of Day Field] - ToTimeOfDay("12:00 am"), Minutes(15));

    ToTimeOfDay("12:00 am") + $RoundedDurationFromMidnight



    I had a look and the Round function will round durations, but not a TimeOfDay field.

    So, my cheat is to change the time of day into the duration from Midnight, and round that Duration.  But now we have a duration and we need a TimeOfDay.  So then I add that Duration to midnight,  to get back to the Time Of Day rounded.
    • JustinPauga's avatar
      JustinPauga
      Qrew Cadet
      8 years ago
      Awesome, this worked great!
      In case anyone runs into this issue as well, if the [My Time Of Day Field] is a date/time like mine is then you have to use the ToTimeOfDay function on that as well so your formula would look like: 

      var duration RoundedDurationFromMidnight =

      Round(_ToTimeOfDay([My Time Of Day Field]_) - ToTimeOfDay("12:00 am"), Minutes(15));

      ToTimeOfDay("12:00 am") + $RoundedDurationFromMidnight