Excel with graphs to show if an SLA has been met or not

[Nat2710]

Hi All,



I'm just back from mat leave after having twins, I am seriously sleep-deprived and in need of help (My brain isn't working at full power)



Trying to create an excel to show if SLAs are being met, along with the percentages in the form of a graph, so far I have gotten this far.



SLAs are:

Priority	Response Time in Minutes
P1	30
P2	60
P3	1440
P4	1440

could anyone help me make the changes needed please.



Best wishes,

One worn out mama

 

[awoohaw]

Congrats on the child. Welcome to the forum.
Not sure why you don't won't to share your version of excel, though. Knowing your version will usually get better solutions for you.

Maybe something like this:

Book1
	A	B	C	D	E
1	T Prior	Open	1st Resp	Duration	SLA Met
2	P1	01-04-2024 08:00	01-04-2024 09:35	95	FALSE
3	P1	07-08-2024 11:00	07-08-2024 11:15	15	TRUE
4	P2	01-04-2024 08:00	01-04-2024 09:35	95	FALSE
5	P2	02-08-2024 11:00	07-08-2024 11:15	7215	FALSE
6	P3	01-04-2024 08:00	01-04-2024 09:35	95	TRUE
7	P3	05-08-2024 11:00	07-08-2024 11:15	2895	FALSE
8	P4	01-04-2024 08:00	01-04-2024 09:35	95	TRUE
9	P4	02-08-2024 11:00	07-08-2024 11:15	7215	FALSE
10
11
12	Priority	Minutes Response
13	P1	30
14	P2	60
15	P3	1440
16	P4	1440
17
18		Met	Not Met	Avg Resp Time vs SLA
19	P1	1	1	1.83333
20	P2	0	2	60.9167
21	P3	1	1	1.03819
22	P4	1	1	2.53819
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=(C2-B2)*(24*60)
E2:E9	E2	=D2<=LOOKUP(A2,$A$13:$A$16,$B$13:$B$16)
B19:B22	B19	=COUNTIFS($A$2:$A$9,$A19,$E$2:$E$9,TRUE)
C19:C22	C19	=COUNTIFS($A$2:$A$9,$A19,$E$2:$E$9,FALSE)
D19:D22	D19	=AVERAGEIFS($D$2:$D$9,$A$2:$A$9,$A19)/(LOOKUP($A19,$A$13:$A$16,$B$13:$B$16))

 

[felixstraube]

Do you have to take into account just the working hours? Or if you have for example like in row 5, the dates 5 days apart, you count 24 hours per day? Or just the working hours?
If you count only the working hours, when do you start and end the day?

 

[felixstraube]

If you have to add just the working hours, would this be a start?:

Book9.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Ticket Priority	Ticket Opened	1st Response		Worked time	Worked minutes	SLA Met?		Holidays		Priority	Response time in minutes		Working day start	Working day end
2	P1	01/04/2024 08:00	01/04/2024 09:35		1:35	95	Yes		02/08/2024		P1	30		08:00	18:00
3	P1	07/08/2024 11:00	07/08/2024 11:15		0:15	15	No				P2	60
4	P2	01/04/2024 08:00	01/04/2024 09:35		1:35	95	Yes				P3	1440
5	P2	02/08/2024 11:00	07/08/2024 11:15		23:15	1395	Yes				P4	1440
6	P3	01/04/2024 08:00	01/04/2024 09:35		1:35	95	No
7	P3	05/08/2024 11:00	07/08/2024 11:15		20:15	1215	No
8	P4	01/04/2024 08:00	01/04/2024 09:35		1:35	95	No
9	P4	02/08/2024 11:00	07/08/2024 11:15		23:15	1395	No
Sheet1

Cell Formulas
Range	Formula
E2:G9	E2	=LET(s, B2, e, C2, 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, Holidays) = INT(se), isWorkingTime, (se_time >= $N$2)*(se_time < $O$2), workedMinutes, SUM(isWorkingDay*isWorkingTime), workedHours, MROUND(workedMinutes/60, TIME(0,1,0)), workedTime, workedMinutes/24/60, ticketMinutes, XLOOKUP(A2, $K$2:$K$5, $L$2:$L$5), ticketResolved, IF(workedMinutes >= ticketMinutes, "Yes", "No"), res, IFERROR(HSTACK(workedTime, workedMinutes, ticketResolved), {0,"No"}), res)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Holidays	=Sheet1!$I$2:$I$10	E2:E9

 

[Nat2710]

Congrats on the child. Welcome to the forum.
Not sure why you don't won't to share your version of excel, though. Knowing your version will usually get better solutions for you.

Maybe something like this:
View attachment 115098

Book1
	A	B	C	D	E
1	T Prior	Open	1st Resp	Duration	SLA Met
2	P1	01-04-2024 08:00	01-04-2024 09:35	95	FALSE
3	P1	07-08-2024 11:00	07-08-2024 11:15	15	TRUE
4	P2	01-04-2024 08:00	01-04-2024 09:35	95	FALSE
5	P2	02-08-2024 11:00	07-08-2024 11:15	7215	FALSE
6	P3	01-04-2024 08:00	01-04-2024 09:35	95	TRUE
7	P3	05-08-2024 11:00	07-08-2024 11:15	2895	FALSE
8	P4	01-04-2024 08:00	01-04-2024 09:35	95	TRUE
9	P4	02-08-2024 11:00	07-08-2024 11:15	7215	FALSE
10
11
12	Priority	Minutes Response
13	P1	30
14	P2	60
15	P3	1440
16	P4	1440
17
18		Met	Not Met	Avg Resp Time vs SLA
19	P1	1	1	1.83333
20	P2	0	2	60.9167
21	P3	1	1	1.03819
22	P4	1	1	2.53819
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=(C2-B2)*(24*60)
E2:E9	E2	=D2<=LOOKUP(A2,$A$13:$A$16,$B$13:$B$16)
B19:B22	B19	=COUNTIFS($A$2:$A$9,$A19,$E$2:$E$9,TRUE)
C19:C22	C19	=COUNTIFS($A$2:$A$9,$A19,$E$2:$E$9,FALSE)
D19:D22	D19	=AVERAGEIFS($D$2:$D$9,$A$2:$A$9,$A19)/(LOOKUP($A19,$A$13:$A$16,$B$13:$B$16))

Thank you, i have updated my version. I didn't realise I hadn't shared it.

This looks great!

 

[Nat2710]

Do you have to take into account just the working hours? Or if you have for example like in row 5, the dates 5 days apart, you count 24 hours per day? Or just the working hours?
If you count only the working hours, when do you start and end the day?

No the SLA runs 24/7 so no working times need to be considered

 

[awoohaw]

Thank you, i have updated my version. I didn't realise I hadn't shared it.

This looks great!

My pleasure.

Best Wishes!