Formula for converting a timestamp to show 24 hour time

Question

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
------------------------------

jeffpeterson1 · Answer

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 · Answer

Thx for posting.&nbsp; I have added this knowledge to the Borg collective.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

austink · Answer

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 · Answer

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 · Answer

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.

Forum Discussion

Formula for converting a timestamp to show 24 hour time