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

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

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

- INT()
- Round out the division results to a 0 decimal integer to avoid data type inconsistencies for outer string conversion

- 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

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

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

- INT()
- Round out the division results to a 0 decimal integer to avoid data type inconsistencies for outer string conversion

- 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

- “0” +
- Add a leading 0 to the string when resulting minutes is only a single digit

- 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

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

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

- INT()
- Round out the division results to a 0 decimal integer to avoid data type inconsistencies for outer string conversion

- 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

- “0” +
- Add a leading 0 to the string when resulting seconds is only a single digit

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

- SUM(milliseconds)
- Use a Sum so the label is additive and flexible depending on the level of detail in the sheet.

- 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

- 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

- RIGHT(,3)
- Pull the 3 digit milliseconds with leading zeros to the right of the decimal

Hey David. Why does everyone call it military time? It’s simply a 24-hour clock 😉

LikeLike

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?

LikeLiked by 1 person