1 2 Previous Next 20 Replies Latest reply on Nov 8, 2019 6:57 AM by Jonathan Drummey

# Convert numbers to hours:minutes

Hello,

I have a field with numbers and I want to show this numbers as hours and minutes.

For example:

3,5 should appear as 3:30 or

100,75 is 100:45

Is this possible and how?

Werner

• ###### 1. Re: Convert numbers to hours:minutes

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

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

• ###### 3. Re: Convert numbers to hours:minutes

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

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:

+ ":" +

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.

• ###### 5. Re: Convert numbers to hours:minutes

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.

Jonathan

4 of 4 people found this helpful
• ###### 6. Re: Convert numbers to hours:minutes

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

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

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

@clay, are you still looking for help with this?

• ###### 10. Re: Convert numbers to hours:minutes

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.

• ###### 11. Re: Convert numbers to hours:minutes

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

Jonathan

1 of 1 people found this helpful
• ###### 12. Re: Convert numbers to hours:minutes

Thanks a ton ! That works perfectly

• ###### 13. Re: Convert numbers to hours:minutes

Johnathan, what if I need to show it as hh:mm:ss?

• ###### 14. Re: Convert numbers to hours:minutes

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

1 2 Previous Next