Displaying Hours and Minutes in Tableau

Steve Fenn recently shared a MoM viz that dealt with a "Time" field on Twitter. He also graciously shared the calc he used to show the Datetimes as "Time"....See currently, there is no DATEPART, or Custom Date Types for Time in Tableau, which can lead to some frustrations. Now, there are some incredible examples out there for how people have gotten around this by transforming the Date field into an INT (based on the Epoch Date) and some math, as well as good ol' String conversions techniques. There are pluses and minus to these techniques, whether it be Performance issues, or just complicated calcs.

So, here is a simple approach I have used, that is a little tricky, but seems to work well (for the most part). I'm not sure if anyone has demonstrated this before, and I can't recall if I've seen it, so if you have seen this, please let me know and I'll give a link back!

It's a bit of trickery and Custom Formats.

To begin, I preface this by saying I am working with a Datetime field in Tableau. Ok, here we go. So right now, my data looks something like this.

This is a very simple dataset that has a Datetime field, and a Record count. My goal is to show the Average Calls by Hour and Minute as a Bar Chart, like below.

Seems simple enough, except Tableau doesn't have a "Time" Datepart, or Custom Date Type option, so showing it in this way can be problematic. As mentioned before, there are a lot of great techniques to getting there, like converting to INT, and using some math, or by string parsing concatenations, but I'm going to take a slightly different approach.

While Tableau does not have a Hour/Minute Combined Datepart, Tableau does allow you to Format Dates to show as Hour/Date, when you are using a DATETRUNC option. However, DATETRUNC('minute', [Datetime]), still retains the "Date" of the Datetime field, so if you were to use custom formatting, your Axis would still show it by Hour and Minute, and Day (the "Day) would simply be invisible.

So how do we work around this? Well, what don't we just make all "Dates" the same Day? That way when you use the DATETRUNC('minute', [Date]) option, all "Dates" become normalized, and we are left with only the Hour and Minutes part of it. To do this, we simply find the Datediff in Days from our Date Field, to a centralized Date . This example uses TODAY(), but really, any Date would work. Once we have the difference in days from the Centralized Date and our Datefield, we do a Date Add to transform all those Dates in the Same Date.

DATEADD('day', DATEDIFF('day', [Datetime Field] ,TODAY()), [Datetime Field])

Even easier solution is to INT the date and subtract that from the date field, which is less code, and probably faster. This works because hours,minutes and seconds are held as decimal places.

[Datetime Field] - INT([Datetime Field])

Now that all the "Dates" are the same, we can simply drag this onto the Columns Shelf, and choose the DATETRUNC('minute', [DATE]) option.

Now, by default, Tableau is going to display this in the full Datetime format...but we don't want that, we want to show as simply Hour Minute AM/PM. So we need to tell Tableau to display it in that format. Right click on the Datetime field click format -> Custom -> and enter HH:MM AM/PM