KNOWLEDGE BASE

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

Published: 04 May 2017

### Question

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

### Environment

Tableau Desktop

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)ELSEIFLEN(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])`