Skip to main content
Question

Formula for converting a timestamp to show 24 hour time

  • September 24, 2021
  • 0 replies
  • 330 views
SSNJeffP
Forum|alt.badge.img+11
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
------------------------------

    SSNJeffP
    Forum|alt.badge.img+11
    • SSNJeffP
    • Author
    • Registered
    • September 27, 2021
    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
    ------------------------------
      Terms & ConditionsAccessibility statement