Discussions

Expand all | Collapse all

Rounding Time to nearest quarter

  • 1.  Rounding Time to nearest quarter

    Posted 11-13-2020 12:59
     I was wondering if there was a way to round the time in/out to the nearest quarter hour?

    This is my current formula:

    var duration RoundedClockIn = Round([Clock In Date Time] - ToTimeOfDay("12:00 am"), Minutes(15));

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

    I keep receiving this error "The operator "-" can't be applied on types date/time, timeofday.

    Any help would be greatly appreciated 


    ------------------------------
    Wallace Yeboah
    ------------------------------


  • 2.  RE: Rounding Time to nearest quarter

    Posted 11-13-2020 20:04
    try this

    var datetime MidnightOfClockInDay = ToTimestamp(ToDate([Clock In Date Time]), ToTimeOfDay("12:00 am"));

    var duration RoundedDuratonfromMidnight = Round([Clock In Date Time] - $MidnightOfClockInDay, Minutes(15));

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

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Rounding Time to nearest quarter

    Posted 11-14-2020 12:27
    Thanks for the response Mark! I'm still getting this error for the last line of code:


    "Expecting duration but found timeofday"

    ------------------------------
    Wallace Yeboah
    ------------------------------



  • 4.  RE: Rounding Time to nearest quarter

    Posted 11-14-2020 12:36
    Actually disregard that last message. Its working now. Thanks a lot!

    ------------------------------
    Wallace Yeboah
    ------------------------------



  • 5.  RE: Rounding Time to nearest quarter

    Posted 04-23-2021 06:33
    Edited by Courtneah Thomas 04-23-2021 06:40
    Wallace Yeboah,  how did you get it to work?  I am getting an error message on the last line that says "expecting text but found time of day"

    ------------------------------
    Courtneah Thomas
    ------------------------------



  • 6.  RE: Rounding Time to nearest quarter

    Posted 04-26-2021 08:30
    Hi Courtneah,

    the ToTimeOfDay function will convert the return to a time of day data type, if your formula is expecting text, then your field type is formula-text. You can either update this to Date/Time, or use one of the other conversion functions (ToText). Though I'm not sure what kind of display you'll get converting this to text.

    I'd recommend changing your field type to Date/Time or Duration depending on your use case.


    ------------------------------
    Kevin Haigler
    ------------------------------