Werner, if you post an Excel spreadsheet with a few number values formatted with your number format (comma instead of a period for decimal), I think I can work this out for you.
Sorry Werner, I can't open your workbook. Yours is the third one that's given me trouble in the past few days. So I assume it's something on my end. Hopefully someone else will jump in with the answer.
I'm not sure if this is the most efficient way at going about converting the numeric values into a timestamp but I've used the following calculated fields to arrive at the requested formatting:
[hour to seconds] - converts the Hour section of the value into seconds:
(INT([Format as is]) * 60) * 60
[get minutes] - takes the decimal value and converts it into minutes:
60 * ([Format as is] - INT([Format as is]))
[minutes to seconds] - converts the minutes from the above value into seconds:
[get minutes] * 60
[add all seconds] - adds the Hour seconds and the Minute seconds together:
SUM([hour to seconds]) + SUM([minutes to seconds])
[create time] - uses the total number of seconds to create a timestamp:
STR(INT([add all seconds] / 3600))
+ ":" +
IF INT([add all seconds] % 3600 / 60) < 10
STR(INT([add all seconds] % 3600 / 60))
+ ":" +
IF INT([add all seconds] % 3600 % 60) < 10
STR(INT([add all seconds] % 3600 % 60))
Hopefully this should work well in the production data set.
number to time.twbx 25.4 KB
Here's alternative calc that doesn't do any string manipulation (which can be 1000x slower than number manipulation), and uses Tableau's built-in custom number formats:
1) Convert Format as is to Seconds:
[Format as is] * 3600
2) Create Formatted number:
IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) //minutes + INT([Seconds]/3600) * 100 //hours
3) Apply a Custom Number format of 00:00 to this number:
Here's the view:
Workbook is attached. For other uses of this technique for hh:mm:ss and dd:hh:mm:ss formatting, check out Formatting Time Durations in Tableau | Drawing with Numbers.
hhmm from decimal hour.twbx 20.0 KB
I can't thank you enough for your answer here. I used your example in a line chart, but the axis still displays in seconds. How do I change the axis so that it reflects the formatted values?
For a discrete data formatted column how can we get total at the end in table 8.2
There aren't any John's on this thread, I'm guessing you're referring to me, Jonathan. See Formatting Time Durations in Tableau | Drawing with Numbers for more info on how to work with totals (particularly the addendum).
@clay, are you still looking for help with this?
Hi, Tableau newbie here.
I succedded in following the steps to get correct time in hour:minutes format.
The next step I need to do is take an average of these times, this is when things are going wrong, 1435 minutes show up as 23:91 instead of 23:55.
Here's a detailed description of my problem. I am dealing with data from devices, that send a minute wisw reading, and I am trying to show a stacked bar graph, showing low, normal and high range. The original reagings are in the following format - Date, Normal minutes, Low minutes, High minutes and so on. When i show the data as a day in a column, the conversion from hour to hour:minutes is correct (Daily sheet in attached workbook). The averages shown in 'Averaged' sheet however show 23:91 instead of 23:55, as described earlier. The attached sheet has only the data for normal minutes for simplicity.
Thanks in advance.
1 of 1 people found this helpful
You need to do the aggregation before you do the conversion, here's a revised calc:
IIF(INT(AVG([Seconds (calculated)])/60)%60 == 60, 0,INT(AVG([Seconds (calculated)])/60) %60)
+ INT(AVG([Seconds (calculated)])/3600)*100
Thanks a ton ! That works perfectly
Johnathan, what if I need to show it as hh:mm:ss?
This post was extremely useful I wanted a step ahead of this. I want the total of all the hours:min generated. If direct total is taken then it gives wrong, it should be according to clock total.
Here is the screenshot
That 03:99 is not what is the actual total