Excel Custom Format [H]:MM, Can this work in Qlik Sense, if not how do you work around it

[mathew101]

Hi All,

I've looked but could not find a answer to my questions.

Excel allows the user to deal with time very affectively; currently I use what they call a custom format, :MM this allow hours to go over 24 and keep minutes as base 60 .

Is there a Qlik Sense time format, custom or otherwise that allows this? if not how would you arrive at the same point.

The requirement is to show hours and minutes time spent training, the raw data is given in :MM format. The output must in the same type of format.

     I need to do calculations on these figures to show the total time spent on training per dept. and the time spent per staff in the depts.      and create staff groups and report these also

     In Excel, pivot tables allow me to quickly calculate this, after noting the field data for time spent as :MM, excel total this      correctly (breaking the 24 digit hour limit), I then create a calculated field which I divides the time spent field in :MM by the Staff      No. field.

I would like to put this into Qlik Sense, as dealing the with underlying Data would be a lot better, as would the reporting look more professional etc. etc.

[lironbaram]

yes

there is a function called : interval

[rahulpawarb]

Hello Mathew,

I hope you are doing good!

You can make use of  Time, Hour & Minute functions (Date & Time Functions) to fulfill your requirement. Please refer below sample examples:

// To get the current time in HH:MM:SS format
Time(Now(),'HH:MM:SS') 

//To Extract current Hour 
Hour(Now())

//To Extract current Minutes
Minute(Now())

P.S.: You have to apply certain logic to take care of summation of minutes and converting it into hours, in case if it is greater than 60.

Thank you!

Rahul