In this case, our Duration column is expressed in the number of seconds. We aggregate these seconds using the SUM function and store the value in a variable called __Duration. We can now calculate the hours by dividing __Duration by 3600 seconds in an hour. We use the INT function to return just the integer portion of this division. We store this value in the __Hours variable.
Now that we know how many hours are represented in our __Duration variable, we can compute the remainder of the duration in seconds without those hours. We do this by subtracting __Hours multiplied by 3600 seconds in an hour from our initial __Duration variable and then finding the remainder of this value divided by 3600 seconds in an hour. We use the MOD function to return the remainder of the division operation and store this value in the __HoursRemainder variable.
Once we have our __HoursRemainder variable calculated, if we divide __HoursRemainder by 60 minutes in an hour and take the integer portion using INT, this provides us with the number of minutes, __Minutes. Taking the remainder of this division using MOD gives us the number of seconds, __Seconds.
We now need to return __Hours, __Minutes, and __Seconds as a number with each component in its proper place. So, if we know that we are going to be using the format of HH:mm:ss, then the seconds start in the ones place, minutes start in the hundredths place, and the hours start in the ten thousandths place (ignore the colons). Hence, we multiply __Hours by 10,000, __Minutes by 100, and __Seconds by nothing. Hence, if we have 2 hours, 3 minutes, and 5 seconds, the return value would be 20,305 (20,000 + 300 + 5).
The magic then comes when we apply the custom display formatting developed by Chelsie Eiden. We specify a custom format of 00:00:00. The specification of 00 means to use a leading zero to pad our number if it is a single digit. Hence, for our example of 2 hours, 3 minutes, and 5 seconds, that results in the number 20,305, which is displayed as 02:03:05—or 2 hours, 3 minutes, and 5 seconds!