Forum Discussion

BenjaminGolden's avatar
BenjaminGolden
Qrew Cadet
6 years ago

Preserve 24-Hour Time Format in Formulae

I have a Formula-Text field that has a Time field inserted into it. The field itself is set to display a 24-hour format, but when put into the formula via the ToText() function, I get the standard format. So the way I see it, I have two options:

1. Write a subformula like Right("00" & Hour(), 2) & ":" & Right("00" & Minute(), 2)

or 2. Use a function that's not listed to preserve the format. Is there one available, or should I just go with writing out the function?

------------------------------
Benjamin Golden
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    I think that you will need to come up with your own text formula to preserve the 24 hour format.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • BenjaminGolden's avatar
      BenjaminGolden
      Qrew Cadet
      6 years ago
      Totally fine, just figured it'd be worth asking since I know QuickBase has a couple of hidden formula functions that someone might know about. I should've probably looked in Uservoice first since there is a suggestion with around 50 votes that asks for 24 hour time in formulas, but it'd be a good thing to see in the future. For now, I used the code snippet I wrote out in the OP and it works for what I need it for so no complaints here.

      ------------------------------
      Benjamin Golden
      ------------------------------
      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        3 years ago
        Anyone ever make a formula for this or figure it out? I could make a formula, but would rather copy/paste if it is already made :)

        ------------------------------
        Mike Tamoush
        ------------------------------
    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      3 years ago
      Nevermind. I just did it. Here it is if anyone needs it. It lost the formatting and i dont have tie to go back and indent everything so it is pretty, but it should work.

      var text Orig = ToText([24 Hour Time]);
      var text AMPM = Right($Orig," ");
      var text Prefix = Left($Orig,":");
      var text Suffix = Left(Right($Orig,":")," ");

      var text NewPrefix =
      If($AMPM = "pm",
      If(
      $Prefix = "01","13",
      $Prefix = "02","14",
      $Prefix = "03","15",
      $Prefix = "04","16",
      $Prefix = "05","17",
      $Prefix = "06","18",
      $Prefix = "07","19",
      $Prefix = "08","20",
      $Prefix = "09","21",
      $Prefix = "10","22",
      $Prefix = "11","23",
      $Prefix = "12","12"
      ),

      $AMPM = "am",
      If(
      $Prefix = "12","00"
      ),
      $Prefix
      );

      $NewPrefix & ":" & $Suffix

      ------------------------------
      Mike Tamoush
      ------------------------------