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

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

2 comments

    1. Great question, I think this comes down to a US bias. In the US we commonly say 2:00pm instead of 14:00 like most other places in the world. However, in the military even US soldiers would say 14:00. Hence the nickname Military time. In this post the hours are additive past 24 hours, not sure what to call that?

      Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s