[bluesifi]

Hi

I need to measure performance of incidents based on resolution times, but I
am having problem working out how to do this.

I have 2 columns in a sheet:-
Start (dd/mm/yy hh:mm format) which shows when the incident was raised
Actual (dd/mm/yy hh:mm format) which shows actual resolution time.

I want to measure performance against primary and secondary resolution
times. I want Primary to show if it was resolved within 2 hours of being
raise, and Secondary to show if it was resolved with 4 working hours.

Is this possible, if so what formula should I use.

thanks

Joanne

[Sandy Mann]

Try something like:

=IF(B1-A1<=TIME(2,,),"Within Two Hours",IF(B1-A1<=TIME(4,,),"Within Four
Hours","Over Four Hours"))

Where the Start time is in A1 and the Resolution time is in B1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"bluesifi" wrote in message
...
Hi

I need to measure performance of incidents based on resolution times, but
I
am having problem working out how to do this.

I have 2 columns in a sheet:-
Start (dd/mm/yy hh:mm format) which shows when the incident was raised
Actual (dd/mm/yy hh:mm format) which shows actual resolution time.

I want to measure performance against primary and secondary resolution
times. I want Primary to show if it was resolved within 2 hours of being
raise, and Secondary to show if it was resolved with 4 working hours.

Is this possible, if so what formula should I use.

thanks

Joanne

[David Biddulph[_2_]]

=IF(COUNT(A2:B2)<2,"",IF(B2-A2<=TIME(2,,),"Primary",IF(B2-A2<=TIME(4,,),"Secondary","Out
of time")))
--
David Biddulph

"bluesifi" wrote in message
...
Hi

I need to measure performance of incidents based on resolution times, but
I
am having problem working out how to do this.

I have 2 columns in a sheet:-
Start (dd/mm/yy hh:mm format) which shows when the incident was raised
Actual (dd/mm/yy hh:mm format) which shows actual resolution time.

I want to measure performance against primary and secondary resolution
times. I want Primary to show if it was resolved within 2 hours of being
raise, and Secondary to show if it was resolved with 4 working hours.

Is this possible, if so what formula should I use.

thanks

Joanne

[edvwvw via OfficeKB.com]

bluesifi wrote:
Hi

I need to measure performance of incidents based on resolution times, but I
am having problem working out how to do this.

I have 2 columns in a sheet:-
Start (dd/mm/yy hh:mm format) which shows when the incident was raised
Actual (dd/mm/yy hh:mm format) which shows actual resolution time.

I want to measure performance against primary and secondary resolution
times. I want Primary to show if it was resolved within 2 hours of being
raise, and Secondary to show if it was resolved with 4 working hours.

Is this possible, if so what formula should I use.

thanks

Joanne


Try

=IF((B1-A1)<(2/24),"PRIME",IF((B1-A1)<(4/24),"SECOND","NOT RESOLVED"))


Start time in A1, Finish time in B1 - change the words within the quotation
marks to whatever you want.

edvwvw

--
Message posted via http://www.officekb.com

[bluesifi]

Thanks for the response, they work.

If I then want to change this and break down between High & Medium, which
have different SLA (ie High the primary target is 2 hours and Medium the
primary target is 4 hours) and the result is Yes or No

A1 B1 C1
D1
Actual Resolution Status
Primary
12/01/06 11:56 13/01/06 12:56 Medium No
11/01/06 14:56 11/01/06 15:59 High Yes

I would like to have a column for Primary and another for Secondary. Can I
have a nesting formula to say If C1 = High then IF((B1-A1)<(2/24) else If C1
= Medium then IF((B1-A1)<(4/24)

Hope you understand

regards

Joanne

[edvwvw via OfficeKB.com]

bluesifi wrote:
Thanks for the response, they work.

If I then want to change this and break down between High & Medium, which
have different SLA (ie High the primary target is 2 hours and Medium the
primary target is 4 hours) and the result is Yes or No

A1 B1 C1
D1
Actual Resolution Status
Primary
12/01/06 11:56 13/01/06 12:56 Medium No
11/01/06 14:56 11/01/06 15:59 High Yes

I would like to have a column for Primary and another for Secondary. Can I
have a nesting formula to say If C1 = High then IF((B1-A1)<(2/24) else If C1
= Medium then IF((B1-A1)<(4/24)

Hope you understand

regards

Joanne


This should work - HIGH or MEDIUM in C1

=IF(AND(B1-A1<2/24,C1="HIGH"),"YES",IF(AND(B1-A1<4/24,C1="MEDIUM"),"YES","NO")
)


edvwvw

--
Message posted via http://www.officekb.com

[bluesifi]

Thanks

That is exactly what I was looking for and it works a treat.

Joanne