If Function with TIME and AND

[Newsome79]

hi i wonder if anyone can help, i'm not sure if i'm trying to over complicate things but i'm struggling with a formula.

In essence i have some job type categories where the required completion time is driven by the job type description.

I have the job type, the job load date and the job completion date.

I've been trying to write a formula, to demonstrate i've mocked up a table below:

A B C
1 Job Type Load Time Work Start Time
2 2 hour Major Incident 15:44:30 17:58:20
3 3 hour Major Incident 10:35:03 12:42:33
4 4 hour Minor Incident 14:52:32 15:10:45


So in theory i want to say if A2 is a 2 hour Major incident job request is the work start time in C2, more than 2 hours later than the load time in B2, if so we have not met our contractual target.

I've played around with a few formula but i can't get it to work, currently i'm trying =IF(AND((A2="2 hour Major Incident",(c2-b2>=02:00:00,"OUT OF SLA","")) but i can't get a SUM function to work in an IF formula.

Can anyone tell me where i'm going wrong and what i need to do please?

Any help / suggestions hugely appreciated!

[southward]

Hi,

Give this a try:

Please Login or Register  to view this content.

The "0.08333" is the general value for 2 hours of time.

"0.125" is for 3 hours.
"0.166667" is for 4 hours.

Hope it is helpful.

[cytop]

Alternatively, add a little table somewhere out of the way:

	O	P
1	2 hour Major Incident	0.083333
2	3 hour Major Incident	0.125
3	4 hour Minor Incident	0.166667

The values in Col P are the SLA times (2,3,4 hours) as a fraction of a day, each was entered as a formula
=2/24 or =3/24 etc... if measuring hours
=2*60/1440 if measuring minutes

Then add a formula to Col E (or whereever)

=IF(C2-B2>VLOOKUP(A2, O1:P3, 2, FALSE), "Outside SLA", "OK")

[Newsome79]

Thank you both very much, if i go for the formula option can i just nest all the different timeframes?

=IF(AND(A2="2 hour Major Incident",(C2-B2)>=0.08333),"OUT OF SLA",""),IF(AND(A2="3 hour Major Incident",(C2-B2)>=0.125),"OUT OF SLA",""),=IF(AND(A2="4 hour Minor Incident",(C2-B2)>=0.166667),"OUT OF SLA","")

i ask because I've tried but excel is telling me i'm missing a ).....

[Tony Valko]

=IF(AND(A2="2 hour Major Incident",(C2-B2)>=0.08333),"OUT OF SLA",""),IF(AND(A2="3 hour Major Incident",(C2-B2)>=0.125),"OUT OF SLA",""),=IF(AND(A2="4 hour Minor Incident",(C2-B2)>=0.166667),"OUT OF SLA","")

Assuming the logic is correct...

=IF(AND(A2="2 hour Major Incident",C2-B2>=0.08333),"OUT OF SLA",IF(AND(A2="3 hour Major Incident",C2-B2>=0.125),"OUT OF SLA",IF(AND(A2="4 hour Minor Incident",C2-B2>=0.166667),"OUT OF SLA","")))

[Phuocam]

If A2 starts with the numbers , you can use ...

=IF(C2-B2>=LEFT(A2,2)/24,"OUT OF SLA","")