JeffPeterson1
Qrew Captain
3 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
------------------------------
• 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
------------------------------
• Thx for posting.  I have added this knowledge to the Borg collective.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
• AustinK
Qrew Commander
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.

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