KPI/SLA calculation

[semvd1988]

[FONT=&quot]Hi all,[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]I've got following issue:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]i need to determine KPI %. The KPI is about exceptance time, i've got two date/time column (ticket received (column L) and ticket accepted (column AC)) that i have to match, the threshhold is 4 hours (within 4 hours is positive, above for hours is negative). The difficulty that i can't seem to go around is that the KPI goes between 08:00 in the morning and 18:00 in the evening, and on friday it stops at 18:00 and picks back up on the monday after (same for holidays). Which means:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]- ticket received on monday 17:00 -> got untill 11:00 to accept. afterwards it exceeds KPI[/FONT]
[FONT=&quot]- ticket received on friday 17:00 -> got untill monday after 11:00 to accept. afterwards it exceeds KPI[/FONT]
[FONT=&quot]- ...[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]How do i do this in excel? I have a short version of my file, but i can't seem to be able to post it here.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Help would be much appreciated![/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Knd rgrds,
Sem

[/FONT]

 

[sergioMabres]

Hi Sem,
You could try something like

[COLOR=#444444][FONT=Calibri]=NETWORKDAYS.INTL(A2,B2,1)+(B2-A2)-INT(B2-A2)-IF(DAY(A2)<>DAY(B2),2.583333,1)[/FONT][/COLOR]

where a2 has first day time and b2 second day time
for holidays you have to use the 3rd parameter in NETWOHDAYS.INT
Cheers
Sergio