Forum Discussion

JeffPeterson1's avatar
JeffPeterson1
Qrew Captain
4 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 b...
Formulas and functions
JeffPeterson1's avatar
JeffPeterson1
Qrew Captain
4 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
    4 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
    4 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.

    • JeffPeterson1's avatar
      JeffPeterson1
      Qrew Captain
      4 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
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        4 years ago
        Yeah like I said not a full solution but it might work depending how you need to use it. Does it do the same thing in actions, automations, or pipelines? That would be a question for the next person looking at this probably.