Announcements
cancel
Showing results for
Did you mean:
Partner - Contributor II

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

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:
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

*/``````

Labels (5)

• ### work hours

1 Solution

Accepted Solutions
Partner - Master III

hi

this expression will work for you

``interval(floor(sum([Total working minutes])/480)+frac(sum([Total working minutes])/480)/3)``
4 Replies
Partner - Master III

hi

this expression will work for you

``interval(floor(sum([Total working minutes])/480)+frac(sum([Total working minutes])/480)/3)``
Partner - Contributor II
Author

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

Hi,

``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,

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Partner - Contributor II
Author

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

Community Browser