Need to calculate the SLA time

[geethads77]

Hi Team,

I have data where i need to calculate the dates for the tickets completed within SLA between 2 dates, i.e. created date and closed date. the table is below, please someone help.

[TABLE="width: 880"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Date/Time Created[/TD]
[TD]Due Date [/TD]
[TD]Date/Time Completed[/TD]
[TD]Status[/TD]
[TD]Priority[/TD]
[TD][/TD]
[TD][/TD]
[TD]Vendor Name[/TD]
[/TR]
[TR]
[TD]1301672598[/TD]
[TD]01.02.2019 08:56:45[/TD]
[TD]11.02.2019 08:56:45[/TD]
[TD]13.03.2019 14:04:20[/TD]
[TD]Completed[/TD]
[TD]Medium[/TD]
[TD][/TD]
[TD][/TD]
[TD]JLL - Technical[/TD]
[/TR]
</tbody>[/TABLE]

Regards,
Geetha

 

[Sam_D_Ben]

Hi,

Please check if this works.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Date/Time Created	Due Date	Date/Time Completed	Status	Priority	Vendor Name	SLA	Net Days	Remarks		Month
2	1301672598	01.02.2019 08:56:45	11.02.2019 08:56:45	13.03.2019 14:04:20	Completed	Medium	JLL - Technical	10 days	40 days	Exceeding SLA		01	Jan
3												02	Feb
4												03	Mar
5												04	Apr
6												05	May
7												06	Jun
8												07	Jul
9												08	Aug
10												09	Sept
11												10	Oct
12												11	Nov
13												12	Dec
14
Sheet4

Cell Formulas
Range	Formula
H2	=(LEFT(C2,2)&"-"&VLOOKUP(MID(LEFT(C2,10),4,2),$L$2:$M$13,2,0)&"-"&MID(C2,9,2))-(LEFT(B2,2)&"-"&VLOOKUP(MID(LEFT(B2,10),4,2),$L$2:$M$13,2,0)&"-"&MID(B2,9,2))&" days"
I2	=(LEFT(D2,2)&"-"&VLOOKUP(MID(LEFT(D2,10),4,2),$L$2:$M$13,2,0)&"-"&MID(D2,9,2))-(LEFT(B2,2)&"-"&VLOOKUP(MID(LEFT(B2,10),4,2),$L$2:$M$13,2,0)&"-"&MID(B2,9,2))&" days"
J2	=IF(I1)
L2	="0"&ROW()-1
L3	="0"&ROW()-1
L4	="0"&ROW()-1
L5	="0"&ROW()-1
L6	="0"&ROW()-1
L7	="0"&ROW()-1
L8	="0"&ROW()-1
L9	="0"&ROW()-1
L10	="0"&ROW()-1

Thanks Muz

 

[Sam_D_Ben]

Sight change.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	ID	Date/Time Created	Due Date	Date/Time Completed	Status	Priority	Vendor Name	SLA (Days)	Net Days	Remarks
2	1301672598	01.01.2019 08:56:45	11.01.2019 08:56:45	05.12.2019 14:04:20	Completed	Medium	JLL - Technical	10	338	Exceeding SLA
Sheet4

Cell Formulas
Range	Formula
H2	=(LEFT(C2,2)&"-"&VLOOKUP(MID(LEFT(C2,10),4,2),$L$2:$M$13,2,0)&"-"&MID(C2,9,2))-(LEFT(B2,2)&"-"&VLOOKUP(MID(LEFT(B2,10),4,2),$L$2:$M$13,2,0)&"-"&MID(B2,9,2))
I2	=(LEFT(D2,2)&"-"&VLOOKUP(MID(LEFT(D2,10),4,2),$L$2:$M$13,2,0)&"-"&MID(D2,9,2))-(LEFT(B2,2)&"-"&VLOOKUP(MID(LEFT(B2,10),4,2),$L$2:$M$13,2,0)&"-"&MID(B2,9,2))
J2	=IF(I2>H2,"Exceeding SLA","With in SLA")

Excel 2013/2016
	L	M
1	Month
2	01	Jan
3	02	Feb
4	03	Mar
Sheet4

Cell Formulas
Range	Formula
L1	Month
L2	01
L3	02
L4	03
M2	Jan
M3	Feb
M4	Mar

Please note.

in L-Column, numbers should be in ('01, '02, '03) format.

Thanks Muz.