Convert total number of minutes to dd-hh:mm format BUT count only 8 hours a day

[Maarten]

Hi,

• I have for each employee a total number of working minutes (integer). 
• I would like to see this number being converted to another format (dd-hh:mm)
• I tried via floor to build the string, but in that case the totals do not work (logic, it's a string).
• I tried via interval function, but that one is not aware that my days only count 8 hours

I have added a simplified example of my issue. Any help is appreciated.

TestData:
load * inline [
Employee,Team,Total working minutes
A,Team-1,1440
B,Team-2,1400
C,Team-1,1500
];

/* 

Expected outcome format (dd-hh:mm):

A worked: 3-00:00
B worked: 2-07:20
C worked: 3-01:00

When grouped per team:
Team-1 total: 6-01:00
Team-2 total: 2-07:20

*/

(edit: forgot to add attachment)

[lironbaram · Accepted solution]

hi 

this expression will work for you 

interval(floor(sum([Total working minutes])/480)+frac(sum([Total working minutes])/480)/3)

[Maarten]

Thanks a lot for the very fast reply and accurate solution.

[Taoufiq_Zarra]

Hi,

what you think about this solution ?

DIV([Total working minutes],480)&'-'&if(Div(Mod([Total working minutes],480),60)<9,'0'&Div(Mod([Total working minutes],480),60),Div(Mod([Total working minutes],480),60))&':'&if(Mod(Mod([Total working minutes],480),60)<9,'0'&Mod(Mod([Total working minutes],480),60),Mod(Mod([Total working minutes],480),60)) as Result,

The idea behind it:

DIV(number of minutes,480) returns an integer that represents the number of days (480=8*60).
Mod(number of minutes,480) the rest of the division that I divide it in the same way on 60 to return the number of hours ... etc.

The &'0' trick is just to harmonize the display of minutes and hours below 9.

The attached Qlik file is loaded with the new formula, a preview:

to use it as an expression for the Team  for example, you replace [Total working minutes] with sum([Total working minutes])

Cheers,

[Maarten]

Hi,

Thanks for your reply! Seems to do the trick, but is quite a bit more complex compared to the interval function solution. On the other hand, learned a new method :). Thanks again.

Maarten