Calculating SLA with two date fields considering only working hours and weekdays

[PrachiKulkarni]

Calculating SLA with ticket created date(timestamp) and first responce time(timestamp) considering only working hours from 8AM to 5Pm and excluding Weekends SAT&SUN. Also need SLA to be in ''hh.mm.ss" format.

for e.g. if created on date is 19.06.2024 10:11:00

          first responce time is  21.06.2024 11:11:00   

                         SLA will be  19:11.00

e.g.2  

if created on date is 19.06.2024 10:11:00

          first responce time is  24.06.2024 11:11:00   

                         SLA will be  28:11.00 which means it took 28 hr 11min and 00 sec to respond 

[brunobertels]

Hi 

Kind of same demand here replied by @marcus_sommer 

https://community.qlik.com/t5/App-Development/Time-taken-for-an-Ticket-to-resolve-Considering-saturd... 

[PrachiKulkarni]

I have tried

 https://community.qlik.com/t5/QlikView-Documents/Working-Hours-Calculation-excluding-weekends-and-ho...

but not working for me.

[brunobertels]

Hi 

Take care to the date, the  time and timestamp format 

but try this 

Assuming a created date aned first response date are always on a working day you can split your mesure like this 

duration between end of day of the created date at 17:00 and created date 

'19/06/2024 17:00:00' - '19/06/2024 10:11:00' = 06:49:00

duration between first response date and opening business : 

'24/06/2024 11:11:00' - '24/06/2024 08:00:00' = 03:11:00 

networkdays between created date + 1 and first response date - 1 * 09:00:00 

it will give you : 

interval(
dayend(Date(floor('19/06/2024 10:11:00'),'DD.MM.YYYY'))-0.291666
-
'19/06/2024 10:11:00'
)

+

interval(
'24/06/2024 11:11:00'
-
(DayStart(Date(floor('24/06/2024 10:11:00'),'DD.MM.YYYY'))+maketime(8))

)

+

interval(NetWorkDays(Date(floor('19/06/2024 10:11:00')+1,'DD.MM.YYYY'),Date(floor('24/06/2024 11:11:00'),'DD.MM.YYYY')-1)*'09:00:00')

[PrachiKulkarni]

Thank you so much @brunobertels for your response!

[PrachiKulkarni · Accepted solution]

I have reffered Solved: Calculate hours between two Date/Time strings - Qlik Community - 380745

to be specific 7th reply. And it worked for me!!!!!