Forum Discussion

AndreonnaGarret's avatar
AndreonnaGarret
Qrew Assistant Captain
5 years ago

Maximum Date Summary Field - Midnight Issues

hey guys!

I am having an issue with a date/time summary field. I have a child table with a date/time out field. this is on a report where these show as child records in a report on a main service report. I have an automated field showing when the main record is closed, which is supposed to be based on the most recent time entry from the child records. At first glance it seems to work. However, when the time is midnight it's not behaving properly. 

So I have 2 child records with the following date/time out:
11/15/19 @ 2am
11/15/19 @ 12am (midnight)

So it should show the midnight entry as the most recent, but it's showing the 2am entry as most recent. Which I get the logic behind, but I need it to not do that. If I change the midnight entry to 11:59am then it works as well, however, this looks very odd on our service reports.

Does anyone know of a work around to make this function properly without having to put midnight as 11:59am when we are talking the end of the day?​​

------------------------------
Thanks!
Andreonna
------------------------------
Relationships

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    5 years ago
    I'm not sure i 100% understand the issue, but if you want to re categorize the date or the time that a certain timestamp is done, like the edge case of midnight, then you can make a formula field mirror to adjust by formula to 11:59 and based your summary fields on that artificial field.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • DavidIsely's avatar
      DavidIsely
      Qrew Member
      4 years ago
      I want to span midnight for a duration. The duration needs to have the date from the first day but allow total minutes to add for the start time before midnight to the stop time after midnight. I have 2 "time of day" fields to capture the start and stop times and then a duration formula to calculate the total time. This works perfectly until a time spans midnight and then I get a negative number. Do you have any suggestions?

      ------------------------------
      David Isely
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        4 years ago
        not tested by try this

        var duration DurationRaw = [TOD End] - [TOD Start];

        IF($DurationRaw > Minutes(0), $DurationRaw, Hours(24) + $DurationRaw)

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