KNOWLEDGE BASE

How to Change Military Time Into Standard 12-Hour Time Format


Published: 04 May 2017
Last Modified Date: 24 Jul 2017

Question

How to convert a military time into a standard 12-hour format.

Environment

  • Tableau Desktop

Answer

To convert a 24-hour integer-based time format, that may contain either three values such as 735 or four values such as 1348, follow the steps outlined below.

Step 1

First, separate the hours from the minutes while ensuring that we do not add extra values due to the different lengths possible for the integer value.
  • Create the following calculation titled ‘Scheduled Trimmed Mil Hour’ in the example using the following formula.
IF
LEN(STR([Scheduled Departure Time])) = 4 THEN LEFT(STR([Scheduled Departure Time]),2)
ELSEIF
LEN(STR([Scheduled Departure Time])) = 3 THEN LEFT(STR([Scheduled Departure Time]),1)
END  

Step 2

Now we need to separate the minutes from the hours.
  • Create the following calculation titled ‘Scheduled Minutes’ in the example using the following formula.
RIGHT(STR([Scheduled Departure Time]),2)

Step 3

Next we need to determine if the time is AM or PM.

  • Create the following calculation titled ‘Scheduled AM/PM’ in the example using the following formula:
IF INT([Scheduled Trimmed Mil Hour]) > 12 THEN 'PM' ELSE 'AM' END

Step 4

Now we need to incorporate all these values into one single field.
  • Create the following calculation titled ‘Scheduled Standard Time’ in the example using the following formula.
STR([Scheduled Standard Hour])+':'+[Scheduled Minutes]+ ' ' + [Scheduled AM/PM]

Step 5

Lastly we need to have the new field recognized as an actual time field by Tableau Desktop.
  • Create the following calculation titled ‘Scheduled Time Date Parsed’ in the example using the following formula.
DATEPARSE('hh:mm a', [Scheduled Standard Time])
Did this article resolve the issue?