SLA Time Calculation

[Muka]

Need SLA Hour calculation base on created date and Resolved date.
1. Holiday given
2. Weekend given
3. SLA Priority and TAT hour given.

CREATION DATE	MODIFY DATE	CLOSING TIME	LAST REPLIER NAME	RESOLVED DATE	Priority	SLA Hour		Holiday	02/08/2024​	Weekend Holiday	Saturday and Sunday
06/08/2024 11:11​	########​	06/08/2024 11:11​	westsaathi@gmail.com	06/08/2024 11:11​	P4	HH:MM:SS
06/08/2024 11:00​	########​	06/08/2024 11:04​		06/08/2024 11:04​	P1
06/08/2024 10:16​	########​	06/08/2024 10:31​		06/08/2024 10:31​	P1				SLA	Hour
06/08/2024 09:53​	########​	06/08/2024 09:57​		06/08/2024 09:57​	P1				P1	4​
06/08/2024 09:45​	########​	06/08/2024 09:49​		06/08/2024 09:49​	P1				P2	8​
05/08/2024 20:01​	########​	-	ramanjeet.singh	06/08/2024 10:47​	P3				P3	32​
05/08/2024 20:00​	########​	-	rahul.das	06/08/2024 10:45​	P3				P4	48​
05/08/2024 19:41​	########​	-	ramanjeet.singh	06/08/2024 10:48​	P3
05/08/2024 17:10​	########​	05/08/2024 17:18​		05/08/2024 17:18​	P1
05/08/2024 17:07​	########​	05/08/2024 17:19​		05/08/2024 17:19​	P4
05/08/2024 17:01​	########​	05/08/2024 17:01​	westsaathi@gmail.com	05/08/2024 17:01​	P1
05/08/2024 15:29​	########​	-		05/08/2024 15:36​	P4
05/08/2024 14:58​	########​	05/08/2024 15:06​	Panja enterprises	05/08/2024 15:06​	P4
05/08/2024 14:52​	########​	05/08/2024 14:57​	westsaathi@gmail.com	05/08/2024 14:57​	P4
05/08/2024 14:30​	########​	-	lalit.thakur	05/08/2024 14:49​	P3
05/08/2024 14:06​	########​	05/08/2024 14:40​		05/08/2024 14:40​	P3
05/08/2024 13:45​	########​	-		05/08/2024 15:02​	P4
05/08/2024 13:43​	########​	-		05/08/2024 15:02​	P4
05/08/2024 13:11​	########​	05/08/2024 15:40​	Danbahua	05/08/2024 15:40​	P1
05/08/2024 13:09​	########​	-	pankaj.kumar	05/08/2024 14:46​	P3
05/08/2024 12:45​	########​	05/08/2024 12:45​	westsaathi@gmail.com	05/08/2024 12:45​	P1
05/08/2024 12:34​	########​	-	rajanikant.tripathi	05/08/2024 12:55​	P3
05/08/2024 12:30​	########​	05/08/2024 12:37​		05/08/2024 12:37​	P1
05/08/2024 12:23​	########​	05/08/2024 14:05​		05/08/2024 14:05​	P4
05/08/2024 11:58​	########​	05/08/2024 11:58​	westsaathi@gmail.com	05/08/2024 11:58​	P1

 

[felixstraube]

Can you explain in more detail how the SLA hours should be calculated? What does the P1-P4 mean? That you work 48 h per day on the proyect for P4 for example?
Is this example data correct? For row 1 you have a proyect completed in 0 hours (creation and resolve datetimes are the same).

 

[Muka]

Can you explain in more detail how the SLA hours should be calculated? What does the P1-P4 mean? That you work 48 h per day on the proyect for P4 for example?
Is this example data correct? For row 1 you have a proyect completed in 0 hours (creation and resolve datetimes are the same).

Our Business Working Hour is 12 hour. For P1 i.e Priority 1 it is 4 Hr it mean ticket should close in 4 hour. If it is not it ment Not Achived. If ticket resolved within 4 hour then Achived. Similarly it is for P2 ,P3 and P4. For P2 it is 8 hour For P3 it is 32 hour and For P4 it is 48 hour.

 

[felixstraube]

Thanks for the response. Let me get this straight.
So you calculate SLA Hours, as the time from "CREATION DATE" to "RESOLVE DATE" excluding holidays and weekends.
And then in another column you want to show/calculate if the ticket is resolved comparing the SLA hours with the hour per priority, that is if for example it is P4 and SLA Hours is 4 hours or greater, then it should return a true (or some text)?

 

[felixstraube]

I mean 48 hours for P4

 

[felixstraube]

How about this?:
I'm assuming that working hours are from 8:00 to 20:00 (which can be adjusted in the formula).

Book9.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CREATION DATE	MODIFY DATE	CLOSING TIME	LAST REPLIER NAME	RESOLVED DATE	Priority	SLA Hour	Ticket Resolved			Holiday		SLA	Hour
2	2024-08-06 11:11			westsaathi@gmail.com	2024-08-06 11:11	P4	0:00	FALSE			02/08/2024		P1	4
3	2024-08-06 11:00				2024-08-06 15:04	P1	4:04	TRUE					P2	8
4	2024-08-06 10:16				2024-08-06 10:31	P1	0:15	FALSE					P3	32
5	2004-08-06 09:53				2024-08-06 09:57	P1	0:00	FALSE					P4	48
6	2004-08-06 09:45				2024-08-06 09:49	P1	0:00	FALSE
7	2004-08-05 00:01			ramanjeet.singh	2024-08-06 10:47	P3	0:00	FALSE
8	2004-08-05 00:00			rahul.das	2024-08-06 10:45	P3	0:00	FALSE
9	2004-08-05 19:41			ramanjeet.singh	2024-08-06 10:48	P3	0:00	FALSE
10	2004-08-05 17:10				2024-08-05 17:18	P1	0:00	FALSE
11	2004-08-05 17:07				2024-08-05 17:19	P4	0:00	FALSE
12	2004-08-05 17:01			westsaathi@gmail.com	2024-08-05 17:01	P1	0:00	FALSE
13	2024-08-05 15:29				2024-08-05 15:36	P4	0:07	FALSE
14	2004-08-05 14:58			Panja enterprises	2024-08-05 15:06	P4	0:00	FALSE
15	2004-08-05 14:05			westsaathi@gmail.com	2024-08-05 14:57	P4	0:00	FALSE
16	2004-08-05 14:30			lalit.thakur	2024-08-05 14:49	P3	0:00	FALSE
17	2004-08-05 14:06				2024-08-05 14:40	P3	0:00	FALSE
18	2004-08-05 13:45				2024-08-05 15:02	P4	0:00	FALSE
19	2004-08-05 13:43				2024-08-05 15:02	P4	0:00	FALSE
20	2004-08-05 13:11			Danbahua	2024-08-05 15:40	P1	0:00	FALSE
21	2004-08-05 13:09			pankaj.kumar	2024-08-05 14:46	P3	0:00	FALSE
22	2004-08-05 01:45			westsaathi@gmail.com	2024-08-05 12:45	P1	0:00	FALSE
23	2004-08-05 01:34			rajanikant.tripathi	2024-08-05 12:55	P3	0:00	FALSE
24	2004-08-05 01:30				2024-08-05 12:37	P1	0:00	FALSE
25	2004-08-05 01:03				2024-08-05 14:05	P4	0:00	FALSE
26	2004-08-05 11:58			westsaathi@gmail.com	2024-08-05 11:58	P1	0:00	FALSE
Sheet3

Cell Formulas
Range	Formula
G2:H26	G2	=LET(s, A2, e, E2, n, ROUND((e-s)/TIME(0,1,0),0), se, VALUE(TEXT(SEQUENCE(n,,s, TIME(0,1,0)), "yyyy-mm-dd hh:mm")), se_time, VALUE(TEXT(MOD(se, 1), "hh:mm")), isWorkingDay, WORKDAY(se-1, 1, Holiday) = INT(se), isWorkingTime, (se_time >= VALUE("08:00"))*(se_time < VALUE("20:00")), workedMinutes, SUM(isWorkingDay*isWorkingTime), workedHours, MROUND(workedMinutes/60, TIME(0,1,0)), workedTime, workedMinutes/24/60, ticketHours, XLOOKUP(F2, $M$2:$M$5, $N$2:$N$5), ticketResolved, workedHours >= ticketHours, IFERROR(HSTACK(workedTime, ticketResolved), {0,FALSE}) )
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Holiday	=Sheet3!$K$2:$K$5	G2:G26

I'm not sure that is what you need, but it is a starting point.