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,

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?

YS

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

And format Date to hh:nn

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

or little bit easier way.

Thanks,

Shin

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

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

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

• 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