Using IF and AND for multiple criterias

Untitled.png

Hi i wanted some help with the IF function. In the Column J "Criteria" -

I want it to Check IF column A "Order Type" is STAT, Routine or Home-Med
IF column A is STAT and Column H is less than or equal to 5 mins - Column I says MET otherwise FAIL
IF column A is Routine and Column H is less than or equal to 15 mins - Column I says MET otherwise FAIL
IF column A is Home-med and Column H is less than or equal to 60 mins - Column I says MET otherwise FAIL


In Columns B - E there are no formulas i just used the Time Format HH:MM:SS
In Column E =

Formula:

Please Login or Register  to view this content.

In Column F =

Formula:

Please Login or Register  to view this content.

In Column H =

Formula:

Please Login or Register  to view this content.

I tried using the basic formula in Column I =

Formula:

Please Login or Register  to view this content.

it returns the error value #NAME?

Any help would really be appreciated

Thank you

In H2 you've got text "4 mins" so you have to agree that is not ="<=5".
Maybe instead of:

Formula:

Please Login or Register  to view this content.

you can use formula which you are using in H, I mean:

Formula:

Please Login or Register  to view this content.

This one works!!

Can i pick ur brain a little more?

Attachment 638845

It should actually Show 61 mins but it shows 1 mins - i think it is taking into account only the minutes and not the hours - How can i fix it

Formula:

Please Login or Register  to view this content.

I can actually use the above code for Column H it returns the value "01 hrs, 1 mins"




But, How do i change this formula to get my required criterias

Formula:

Please Login or Register  to view this content.

Thanks alot

Attachment can't be download.
Try once more.

Still wrong.
Go this way:
Go advanced -> Manage attachments (below main post window) -> on new page on top choose file, Upload - on right, then Close window and Post.

Ok, I will try it that way

In Column H =

Formula:

Please Login or Register  to view this content.

In Column I =

Formula:

Please Login or Register  to view this content.

In Column J =

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I	J
1	STAT	15:30:00	15:32:00	15:33:00	15:35:00	1 mins		5 mins	MET
2	Home-Med	16:00:00	16:15:00	16:25:00	17:01:00	10 mins		61 mins		FAIL

	A	B	C	D	E	F	G	H	I	J
1	STAT	15:30:00	15:32:00	15:33:00	15:35:00	=TEXT(D1-C1,"[m]")& " mins"		=TEXT(E1-B1,"[m]")& " mins"	=IF(OR(AND(A1="STAT",(MINUTE(E1-B1))<=5),AND(A1="Routine",(MINUTE(E1-B1))<=15),AND(A1="Home-med",(MINUTE(E1-B1))<=TIMEVALUE("1:00:00"))),"MET","")	=IF(OR(AND(A1="STAT",(MINUTE(E1-B1))>5),AND(A1="Routine",(MINUTE(E1-B1))>15),AND(A1="Home-med",(MINUTE(E1-B1))>=TIMEVALUE("1:00:00"))),"FAIL","")
2	Home-Med	16:00:00	16:15:00	16:25:00	17:01:00	=TEXT(D2-C2,"[m]")& " mins"		=TEXT(E2-B2,"[m]")& " mins"	=IF(OR(AND(A2="STAT",(MINUTE(E2-B2))<=5),AND(A2="Routine",(MINUTE(E2-B2))<=15),AND(A2="Home-med",(MINUTE(E2-B2))<=TIMEVALUE("1:00:00"))),"MET","")	=IF(OR(AND(A2="STAT",(MINUTE(E2-B2))>5),AND(A2="Routine",(MINUTE(E2-B2))>15),AND(A2="Home-med",(MINUTE(E2-B2))>=TIMEVALUE("1:00:00"))),"FAIL","")

Sheet: Sheet1

5 minutes and 15 minutes you can change for TIMEVALUE("00:00:05") and TIMEVALUE("00:15:00")

It doesn't work i tried using
For Criteria Met

Formula:

Please Login or Register  to view this content.

For Criteria Failed

Formula:

Please Login or Register  to view this content.

They return no values

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.