Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator II
Creator II

Interval with working days

Dear All,

Hopefully this is a nice and simple one, but clearing it's not coming to me today!!

I have a calculation performed in the script to calculate the duration between two days including working time, which works perfect and provides me with an output in hours and minutes. I want to format this to show it in days, hours and minutes, but unfortunately, it shows the number of days (as in 24 hours) for this value rather than a working day-day (as in 8 hours).

So, an example set of data would be:

Date Start: 18/04/2016 - 14:17:42

Date End: 27/041/2016 - 12:18:40

Duration: 54:00:58

.... So far so good!!

In days, this would be 6 Days, 6 Hours 1 Minute....

... However, if I was to do Interval(54:00:58,'DD hh:mm:ss), I get 02 06:00:58 as a 'day' is 24 hours.

Can you advise on what I need to do to format this to get a working day in the result?

Many thanks for your assistance!

Kind Regards,

Dayna

11 Replies
ThornOfCrowns
Specialist II
Specialist II

Based on previous replies, where Finish is your Finish date, Start is your Start date:

LOAD * INLINE [
Start, Finish
18/04/2016 14:17:42, 27/04/2016 12:18:40
]
;

=Interval#(Floor(Interval#(
Interval(

(
RangeMin(frac(Finish), MakeTime(18))

-
RangeMax(frac(Start), MakeTime(8)))

+ (
NetWorkDays(Start, Finish-1) * MakeTime(8)) // Only 8 hours per whole day

)
, 'h:mm:ss')/
MakeTime(8)) & ' ' &
Interval(Frac(Interval#(
Interval(

(
RangeMin(frac(Finish), MakeTime(18))

-
RangeMax(frac(Start), MakeTime(8)))

+ (
NetWorkDays(Start, Finish-1) * MakeTime(8)) // Only 8 hours per whole day

)
, 'h:mm:ss')/
MakeTime(8)) *(8/24) , 'h:mm:ss'), 'D hh:mm:ss')

Dayna
Creator II
Creator II
Author

Wow, thanks for the many responses, many ways to get to the result... Thanks all, much appreciated!!