8 Replies Latest reply on Sep 2, 2018 8:27 PM by Shinichiro Murakami

# How to convert 3 digit number (military time) to general time?

Hi all,

Can someone please help me?

I used DATEPARSE to convert military time (number) to general time.

But it cannot change the 3 digit value.

Would you please let me know how to solve this problem?

Thank you for your help in advance.

YS • ###### 1. Re: How to convert 3 digit number (military time) to general time?
3 of 3 people found this helpful
• ###### 2. Re: How to convert 3 digit number (military time) to general time?
3 of 3 people found this helpful
• ###### 3. Re: How to convert 3 digit number (military time) to general time?

Hi Skye Noh

First add zero before your military number to make it 4 digit, Because military number contains 4 digit.

then here is explanation:

#### 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)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])`

Please mark correct and helpful if it works for you.

Best

Suman

3 of 3 people found this helpful
• ###### 4. Re: How to convert 3 digit number (military time) to general time?

Hi Nitin Paighowal,

Thank you a lot for your kind explanation and help : )

Kind Regards,

YS

• ###### 5. Re: How to convert 3 digit number (military time) to general time?

Dear Shinichiro Murakami San,

とてもありがとうございます : )

Kind Regards,

YS

• ###### 6. Re: How to convert 3 digit number (military time) to general time?

Thanks Skye, if that helped answering your query , please mark it as correct answer for your scenario.

• ###### 7. Re: How to convert 3 digit number (military time) to general time?

Hi Suman Kumar,

Thank you for your kind help : )

` `

Kind Regards,

YS

• ###### 8. Re: How to convert 3 digit number (military time) to general time?

You are very welcome.

どういたしまして。

Shin