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

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
+ ":" +

//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

//Delimiter
+ ":" +

//Seconds with Leading 0
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