We’ve all dealt with the frustration of having a number that represents time in some way. It’s one of the moments when computers’ lack of understanding of context really shines.
A common occurrence is when we have a duration but want to represent it in a way that makes sense. For example, 1 Hour, 30 Minutes and 47 Seconds makes much more sense than 5,447 Seconds.
To date, I've been using modulo calculations to solve this but they’re confusing and need multiple calculations. I found an easier way...
...format numbers as time durations!
Tableau, like excel, doesn’t comprehend the concept of time like you and me. Instead, it counts up from a certain benchmark date: 30/12/1899 (see figure.1).
This means date fields are essentially glorified numbers so we can also force Tableau to format numbers as dates. Both pill formatting and default formatting work. I used the following date format symbols to achieve this: dd/MM/yyyy HH:mm:ss (see here).
If whole numbers represent days then decimals should represent time right? Looking at figure 2, you'll see anything between 0 to 1 represents a 24-hour period.
Let’s push this even further!
So far, we’ve learned to format time (i.e. anything less than one day). But if you look at our conversions again (figure 1), you’ll notice that day two is the turn of the century. By coincidence, this means if we add one day to our numbers, we can accurately format the time for up to, but not including, one year.
For example, if our original duration is 1.16… days, we can add one (i.e. 2.16…) and format it as ‘ dd”day” HH”hrs” mm”mins” ss”secs” ‘ and it’ll be accurately formatted as 1 day 3hrs 56mins 21secs (see third row of figure y).
Enjoy Vizzing!
