Forum Discussion

SSNJeffP's avatar
SSNJeffP
Qrew Captain
5 years ago

Formula for converting a timestamp to show 24 hour time

I have a date/time formula that outputs a value like this:  09-18-2021 04:00 PM

I need to convert it to:  09-18-2021 16:00 

This one is giving me a lot of trouble as there doesn't seem to be a built in function to convert 4:00PM to 16:00 directly.    

Anyone tackle this one before?

------------------------------
Jeff Peterson
------------------------------
Formulas and functions

5 Replies

  • SSNJeffP's avatar
    SSNJeffP
    Qrew Captain
    5 years ago
    Here's the solution in case anyone else searches this.  This is probably not the most elegant way, but it seems to work.

    var bool AMorPM=(Contains(ToText(ToTimeOfDay([My DateTime Field])),"PM")=true); //Is this AM or PM
    var text NewDate=(ToText(ToDate([My DateTime Field]))); //Convert Timestamp to Date only
    var text NewHour=Right(Left(ToText(ToTimeOfDay([My DateTime Field])),":")," "); //Get Hour
    var text NewMinute=Left(Right(ToText(ToTimeOfDay([My DateTime Field])), ":")," "); //Get Minute and Trim off AM/PM
    var text TwentyFourHour= //Correct Hour if PM
    Case($NewHour,
    "12", "12",
    "1", "13",
    "2", "14",
    "3", "15",
    "4", "16",
    "5", "17",
    "6", "18",
    "7", "19",
    "8", "20",
    "9", "21",
    "10", "22",
    "11", "23");
    var text TwelveHour= //Correct Hour if AM
    Case($NewHour,
    "12","00",
    "1", "01",
    "2", "02",
    "3", "03",
    "4", "04",
    "5", "05",
    "6", "06",
    "7", "07",
    "8", "08",
    "9", "09",
    "10", "10",
    "11", "11");


    If($AMorPM=true,
    $NewDate&" "&$TwentyFourHour&":"&$NewMinute,
    $NewDate&" "&$TwelveHour&":"&$NewMinute
    )
    ------------------------------
    Jeff Peterson
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      5 years ago
      Thx for posting.  I have added this knowledge to the Borg collective.

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------
    • AustinK's avatar
      AustinK
      Qrew Commander
      5 years ago
      If you use a Formula Time of Day field you can pull the time of day in military time that way too or the function that does the same, I just used the field to display it on the form here. Might be worth looking into it. This type of formula field has the ability to display the time from your date/time field in various ways and 24 hour is an option on the field properties itself.

      My thinking is it could be tied into another formula but it might need to be displayed first on the form. Sorry it isn't a full solution but it might help if you or someone else wants to explore further. 

      In my screenshot the field "my test for ToD" is a Time of Day formula with the formula of ToTimeOfDay([date test]) and set to display times in military.

      Date test is simply a date/time field I filled in.

      • SSNJeffP's avatar
        SSNJeffP
        Qrew Captain
        5 years ago
        The problem with what you did there is if you use [my test for ToD] in a formula text field,  it will return 4:00 PM instead of 16:00, even if you have that field set to format it to 24 hour clock.

        Here, the field [DateTime] is just a date/time field.  

        [TimeOfDay] is a formula - timeofday field set to 24 hour clock, with the formula:  toTimeofDay[DateTime].   

        The field [Calculated] is a formula text field with the formula:  toText([TimeOfDay]).

        You can see it ignore the format settings and flips back to 1:49 pm.

        ------------------------------
        Jeff Peterson
        ------------------------------