Time and IF

[Frank3923]

I have a work sheet that I need a formula for that will classify the elasped time into 4 different classification. I have attempted 2 different formulas, that I will list below. An example of my desired restults, would be
K1= B-Late, K2=ok, K3=ok, K4=Blank, K5=A-Late, K6=B-Late and So, on.

Column K
=IF(AND(J3>=0,J3<=0.09),"Okay",IF(AND(J3>=0.09,J3<=0.18),"A-Late",IF(AND(J3>=0.18,J3<=10),"B-Late",IF(J3="",""))))

Column L
=IF(AND(J3>="0:00",J3<="0:15"),"Okay",IF(AND(J3>="0:15",J3<="0:30"),"A-Late",IF(AND(J3>="0:30",J3<="010:00"),"B-Late",IF(J3="",""))))

Any advise, or assistance would be appreciated.

LINEHAUL CUT TIMES-PROJECT-050803-Thursday-.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1		5/8/03	TimeSchedules						Page1of3
2	Origin:	Assignment	Name	SchCut	SchDept	SchArr	ActDep	Eta		Time	Review
3	Ana	Fso-Mod-Sac	Vincea	20:00	20:30	2:30	21:45	3:15	wa2	1:15	Okay	FALSE
4	Bak	Lax-Bynd	Willsr	18:00	18:30	20:30	18:30	20:30		0:00	Okay	FALSE
5		Lax-Sac	Collil	21:30	22:00	23:00	22:10	23:15		0:10	Okay	FALSE
6	Eur	Sro-Eur	Chrisp	22:00	22:30	3:00	21:15	1:45		22:45	B-Late	FALSE
7	Fso	Sac-turn	Dalerm	21:00	21:30	0:30	21:50	0:50		0:20	Okay	FALSE
8		Sro-Sac	Fritts	1:00	1:30	3:30	2:30	5:45	driverlatetowork	1:00	A-Late	FALSE
9		Bak-Fso-Mod-Sac	Eckowr	22:00	22:30	2:00	22:45	2:15		0:15	Okay	FALSE
10		Fso-Lax-Fon	Riveez	22:00	22:30	1:30	22:50	1:50	wa2	0:20	Okay	FALSE
Sheet1

 

[Egress1]

Howdy Frank,

Try This

=IF(AND(J3>0,J3 <0.25),"Okay",IF(AND(J3>0.25,J3<=0.5),"A-Late",IF(AND(J3>0.5,J3<=10),"B-Late",IF(J3=0,"","Ahead!"))))

In Column J where you show your time ahead or behind departure use this

=(G3-E3)*24

Format That column as either General or Number.

Rather than giving you "1:15" it will show it in decimal time "1.25"

Does that work for you?

 

[Egress1]

Howdy Frank,

Correction

=IF(AND(J3>0,J3 <=0.25),"Okay",IF(AND(J3>0.25,J3<=0.5),"A-Late",IF(AND(J3>0.5,J3<=10),"B-Late",IF(J3=0,"","Ahead!"))))

add the = sign for <=.25

 

[Frank3923]

Thank you for the response. I will give this a try.