# Display time over 24 hours in Power BI



## ChloeSpurge (Feb 22, 2022)

Hi,

I have this pivot table in excel which sums weekly hours, then I've formatted it as [hh]:mm due to the sum being over 24 hours.






However when I import this table into power BI it will not let me change the time to be over 24 hours, matching the table above. Any ideas how I could makes the times correct (matching table above)? Maybe convert the decimal to a time?


----------



## severynm (Feb 22, 2022)

I'm a bit confused here about your number formats. Take week 24 for instance. Which is one correct: 145320 hrs 41 mins or 6055 hrs 41 mins? Or are they just using different data?

Anyways, greater than 24hr time formats are not something PBI can handle on its own. Assuming the units in your `Sum of Weekly Hours` column is actually number of hours, you could kind of make your own in a new column. This new column will technically be a text column, so you could not apply numerical operations against it, but that's what the original decimal column is for.

```
new column = TRUNC([sum of weekly hours]) & ":" & FORMAT(([sum of weekly hours]-TRUNC([sum of weekly hours]))*60,"00")
```


----------



## ChloeSpurge (Feb 23, 2022)

severynm said:


> I'm a bit confused here about your number formats. Take week 24 for instance. Which is one correct: 145320 hrs 41 mins or 6055 hrs 41 mins? Or are they just using different data?
> 
> Anyways, greater than 24hr time formats are not something PBI can handle on its own. Assuming the units in your `Sum of Weekly Hours` column is actually number of hours, you could kind of make your own in a new column. This new column will technically be a text column, so you could not apply numerical operations against it, but that's what the original decimal column is for.
> 
> ...


Hi, sorry I should have specified, below is the the format power BI turns the data into when it is imported, this is the decimal version of 145320:41, 144277:56 etc. durations. Maybe there's a way to turn the decimal number into a time format? matching 145320:41, 144277:56 etc. 



Many thanks


----------



## RoryA (Feb 23, 2022)

Load the column as a Duration type, then you can use something like:


```
=Number.ToText(Number.RoundDown(Duration.TotalHours([HoursColumn]),0))&":"&Number.ToText(Number.RoundDown(Duration.Minutes([HoursColumn]),0))
```

where HoursColumn is the name of your duration column.


----------



## ChloeSpurge (Feb 23, 2022)

RoryA said:


> Load the column as a Duration type, then you can use something like:
> 
> 
> ```
> ...


Hi, thanks for the reply, I've tried this as a measure and new column but both have this issue pop up, any ideas?


----------



## RoryA (Feb 23, 2022)

What version of PowerBI are you using? You'd do it in the Power Query editor, not as a measure.


----------



## ChloeSpurge (Feb 23, 2022)

RoryA said:


> What version of PowerBI are you using? You'd do it in the Power Query editor, not as a measure.


I've got this version, I don't think its the pro version. How would I use this formula/code in the power query editor?




Many thanks


----------



## RoryA (Feb 23, 2022)

Mine is 2.99.621.0 (Nov 21)

Add a new custom column and paste that formula in.


----------



## ChloeSpurge (Mar 21, 2022)

RoryA said:


> Mine is 2.99.621.0 (Nov 21)
> 
> Add a new custom column and paste that formula in.
> 
> View attachment 58553


Great thanks I'll give this a go!


----------



## ChloeSpurge (Mar 21, 2022)

RoryA said:


> Mine is 2.99.621.0 (Nov 21)
> 
> Add a new custom column and paste that formula in.
> 
> View attachment 58553


Hiya, I tried this but its put the word 'Table' in the new column?


----------



## ChloeSpurge (Feb 22, 2022)

Hi,

I have this pivot table in excel which sums weekly hours, then I've formatted it as [hh]:mm due to the sum being over 24 hours.






However when I import this table into power BI it will not let me change the time to be over 24 hours, matching the table above. Any ideas how I could makes the times correct (matching table above)? Maybe convert the decimal to a time?


----------



## RoryA (Mar 21, 2022)

It looks like you used the formula from post 8 instead of the one from post 4. The one in post 8 includes the functions that the PQ editor adds when you create a custom column, which is why you have ended up with a Table. The formula for the calculated column is just:



```
=Number.ToText(Number.RoundDown(Duration.TotalHours([HoursColumn]),0))&":"&Number.ToText(Number.RoundDown(Duration.Minutes([HoursColumn]),0))
```


----------



## ChloeSpurge (Mar 28, 2022)

RoryA said:


> It looks like you used the formula from post 8 instead of the one from post 4. The one in post 8 includes the functions that the PQ editor adds when you create a custom column, which is why you have ended up with a Table. The formula for the calculated column is just:
> 
> 
> 
> ...


Thank you it worked!


----------



## ChloeSpurge (Apr 29, 2022)

RoryA said:


> It looks like you used the formula from post 8 instead of the one from post 4. The one in post 8 includes the functions that the PQ editor adds when you create a custom column, which is why you have ended up with a Table. The formula for the calculated column is just:
> 
> 
> 
> ...


Hi, this works well, however when the time is say 5:05, 0.21 as a decimal, it comes out the formula as 5:50, is there any why to change this so it shows minutes under 10 as having a 0 before? eg 5:05, 6:09, 7:03 etc


----------



## RoryA (Apr 29, 2022)

Yes, you can use:


```
=Number.ToText(Number.RoundDown(Duration.TotalHours([HoursColumn]),0))&":"&Number.ToText(Number.RoundDown(Duration.Minutes([HoursColumn]),0), "D2")
```


----------



## ChloeSpurge (May 4, 2022)

RoryA said:


> Yes, you can use:
> 
> 
> ```
> ...


Thank you!


----------

