Time taken for an Ticket to resolve-(Considering saturday & sunday & all Holidays)

[poojashribanger]

Hi All,

I have a below table in Qlik were for each TicketID there is a reported and resolved time.

We have to calculate time taken for an ticket to resolve.(considering on 6:00AM to 12:00PM midnight as working hours)

Total working Hours=18

I want Ouptut as below.

TicketId	Reported	Resolved	OutPut
1	12/12/2023 19:23:00	14/12/2023 10:33:00	27:10:00
2	12/01/2024 08:19:00	14/01/2024 10:30:00	38:11:00
3	10/12/2024 19:00:00	11/12/2024 10:00:00	09:00:00
4	24/04/2024 14:30:00	26/04/2024 8:30:00	30:00:00
5	24/04/2024 14:30:00	25/04/2024 1:30:00	09:30:00
6	24/04/2024 14:30:00	25/04/2024 5:30:00	09:30:00
7	25/04/2024 1:30:00	25/04/2024 5:30:00	04:00:00
8	24/04/2024 14:30:00	28/04/2024 1:30:00	63:30:00
9	24/04/2024 20:00:00	25/04/2024 6:30:00	04:30:00
10	24/04/2024 19:48:00	25/04/2024 4:00:00	04:12:00

Could someone please help me on this.

Thanks in Advance

[brunobertels]

Hi 

May be this 

(Date(floor(Resolved),'DD/MM/YYYY')
-
Date(floor(Reported),'DD/MM/YYYY')
-1
)*'18:00:00'

+

interval(dayend(Reported)-Reported)+(1/86400)

+
interval(Resolved-daystart(Resolved)-0.25)

But have a look to the highlighted line in red i do not understand why a ticket could be resolved between 12:00PM and 6:00 AM ?

Hope it helps 

regards

[marcus_sommer]

Look on my answer here: Re: Regional and Country Specific Public Holidays - Qlik Community - 2463343

Update: sorry was the wrong link - meant was: Re: Excluding Non operational Hour's - Qlik Community - 2463134