# Tableau: Convert Milliseconds or Seconds to HH:MM:SS (Military Time) Posted by

Converting milliseconds or seconds to additive military time with >24 hours in the hours bucket involves division, modulo division and orders of operations in equation creation.  I want these to be additive measures, hence the inner SUM. For milliseconds we first convert them to seconds with /1000, so both formulas are essentially the same structure.

This is something I first did back in 2012 while consulting for Tableau onsite in Indianapolis, and I have meant to blog it ever since.  The time is now.

I have included a Tableau TWBX from 2019.4.0 here if you want to reverse engineer.

TLDR; Just Give me the Equation

```//Hours
STR(INT((SUM([milliseconds])/1000/60/60)))

//Delimiter
+ ":" +

IF LEN(STR(INT((SUM([milliseconds])/1000)/60%60)))=1 THEN
"0" + STR(INT((SUM([milliseconds])/1000)/60%60))
ELSE
STR(INT((SUM([milliseconds])/1000)/60%60))
END

//Delimiter
+ ":" +

IF LEN(STR(INT((SUM([milliseconds])/1000)%60)))=1 THEN
"0" + STR(INT((SUM([milliseconds])/1000)%60))
ELSE
STR(INT((SUM([milliseconds])/1000)%60))
END

//Delimiter
+ "." +

RIGHT(STR(1000+SUM([milliseconds])),3)
```

Breaking Down the Equation

```//Hours
STR(INT((SUM([milliseconds])/1000/60/60)))
```
1. (SUM(milliseconds)/1000)
• Use a Sum so the label is additive and flexible depending on the level of detail in the sheet.
• Convert to seconds from milliseconds
2. /60/60
• The first /60 creates minutes from the seconds and the next /60 converts the minutes to hours
• Alternatively you could just divide the milliseconds by 3,600,000 but I wanted to really break it down
3. INT()
• Round out the division results to a 0 decimal integer to avoid data type inconsistencies for outer string conversion
4. STR()
• Wrap the hours statement in a string because this is a label and I want concatenation with the “+” operator and not adding hours to minutes.
```//Minutes with Leading 0
IF LEN(STR(INT((SUM([milliseconds])/1000)/60%60)))=1 THEN
"0" + STR(INT((SUM([milliseconds])/1000)/60%60))
ELSE
STR(INT((SUM([milliseconds])/1000)/60%60))
END
```
1. (SUM(milliseconds)/1000)
• Use a Sum so the label is additive and flexible depending on the level of detail in the sheet.
• Convert to seconds from milliseconds
2. /60%60
• Dividing by 60 converts the seconds to minutes
• Modulo division by 60 (%) results in the remaining minutes that could not be transfered to the hours place after converting the minutes to hours
3. INT()
• Round out the division results to a 0 decimal integer to avoid data type inconsistencies for outer string conversion
4. STR()
• Wrap the minutes expression in a string because this is a label and I want concatenation with the “+” operator and not adding minutes to seconds
5. “0” +
• Add a leading 0 to the string when resulting minutes is only a single digit
6. IF LEN() = 1
• Wrap the minutes expression in an IF clause to find cases where a leading 0 is necessary, else the expression just returns the double digit minutes

```//Seconds with Leading 0
IF LEN(STR(INT((SUM([milliseconds])/1000)/60%60)))=1 THEN
"0" + STR(INT((SUM([milliseconds])/1000)/60%60))
ELSE
STR(INT((SUM([milliseconds])/1000)/60%60))
END
```
1. (SUM(milliseconds)/1000)
• Use a Sum so the label is additive and flexible depending on the level of detail in the sheet.
• Convert to seconds from milliseconds
2. %60
• Modulo division by 60 (%) results in the remaining seconds that could not be transferred to the minutes place after converting the seconds to minutes
3. INT()
• Round out the division results to a 0 decimal integer to avoid data type inconsistencies for outer string conversion
4. STR()
• Wrap the seconds expression in a string because this is a label and I want concatenation with the “+” operator and not adding seconds to minutes
5. “0” +
• Add a leading 0 to the string when resulting seconds is only a single digit
6. IF LEN() = 1
• Wrap the seconds expression in an IF clause to find cases where a leading 0 is necessary, else the expression just returns the double digit seconds

```//Milliseconds to right of decimal
RIGHT(STR(1000+SUM([milliseconds])),3)
```
1. SUM(milliseconds)
• Use a Sum so the label is additive and flexible depending on the level of detail in the sheet.
2. 1000+
• This is a handy trick to avoid having to do more verbose nested IF statements to add 1 and 2 leading zeros respectively.  Just add 1000 to the milliseconds to pad in the 0 or 00 for the outer statements
3. STR()
• Wrap the milliseconds expression in a string because this is a label and I want concatenation with the “+” operator and not adding milliseconds to seconds
4. RIGHT(,3)
• Pull the 3 digit milliseconds with leading zeros to the right of the decimal

1. Phillip Millhollen says:
1. bigdatadave says: