
1. Re: Convert numbers to hours:minutes
Shawn Wallwork Aug 9, 2014 8:14 AM (in response to Werner Dürr)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.
Shawn

2. Re: Convert numbers to hours:minutes
Werner Dürr Aug 11, 2014 11:55 PM (in response to Shawn Wallwork)Hi Shawn,
please find attached the excel demo file. Column A is as I get the values from the Database and column B how it should be in my Report.
Werner

Demo.xlsx 8.6 KB


3. Re: Convert numbers to hours:minutes
Shawn Wallwork Aug 12, 2014 6:27 PM (in response to Werner Dürr)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.
Shawn

4. Re: Convert numbers to hours:minutes
Dan Sanchez Aug 12, 2014 9:31 PM (in response to Werner Dürr)Hi Werner!
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
THEN "0"
ELSE ""
END
+
STR(INT([add all seconds] % 3600 / 60))
+ ":" +
IF INT([add all seconds] % 3600 % 60) < 10
THEN "0"
ELSE ""
END
+
STR(INT([add all seconds] % 3600 % 60))
Hopefully this should work well in the production data set.

number to time.twbx 25.4 KB


5. Re: Convert numbers to hours:minutes
Jonathan Drummey Aug 13, 2014 8:31 AM (in response to Werner Dürr)Here's alternative calc that doesn't do any string manipulation (which can be 1000x slower than number manipulation), and uses Tableau's builtin 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.
Jonathan

hhmm from decimal hour.twbx 20.0 KB


6. Re: Convert numbers to hours:minutes
clay.kitchens Jun 11, 2015 5:59 AM (in response to Jonathan Drummey)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?
Thank you!
Clay

7. Re: Convert numbers to hours:minutes
dinesh reddy Jul 28, 2015 11:26 PM (in response to Jonathan Drummey)hi John
For a discrete data formatted column how can we get total at the end in table 8.2

8. Re: Convert numbers to hours:minutes
Jonathan Drummey Jul 29, 2015 4:38 AM (in response to dinesh reddy)Hi Dinesh,
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).

9. Re: Convert numbers to hours:minutes
Jonathan Drummey Jul 29, 2015 4:38 AM (in response to clay.kitchens)@clay, are you still looking for help with this?

10. Re: Convert numbers to hours:minutes
Abhiram Sahasrabudhe Oct 24, 2016 2:02 AM (in response to Jonathan Drummey)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.

HHMM trial.xls 127.5 KB

HHMM trial.twbx 78.6 KB


11. Re: Convert numbers to hours:minutes
Jonathan Drummey Oct 24, 2016 9:52 AM (in response to Abhiram Sahasrabudhe)1 of 1 people found this helpfulYou 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
Jonathan

12. Re: Convert numbers to hours:minutes
Abhiram Sahasrabudhe Oct 24, 2016 9:55 PM (in response to Jonathan Drummey)Thanks a ton ! That works perfectly

13. Re: Convert numbers to hours:minutes
Ramin Melikov Jun 1, 2017 12:53 PM (in response to Jonathan Drummey)Johnathan, what if I need to show it as hh:mm:ss?

14. Re: Convert numbers to hours:minutes
Sanket Deshpande Dec 15, 2017 2:13 AM (in response to Jonathan Drummey)Hi Jonathan,
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
Kindly help.
Regards,
Sanket