if time take is equal to... Closed

Question

Hello, 




Here is the situation I would like a formula that will tell me if the truck left on time or not. If truck dose leave on time then it would be equal to 1 and if it is a miss then it is equal to 0 depending on what type of truck it is.

B-Double = 2 hours
A Trailer Only = 30 minutes
Single = 1 hour
Rigid = 30 minutes

Hit and Miss	Time Taken	In time	 	 	Out Time	Truck Type
0	0:35	7:00	7:10	7:20	7:35	Rigid
0	0:35	12:00	12:05	12:10	12:35	A Trailer Only 
1	0:25	14:10	14:15	14:30	14:35	B-Double
0	2:30	13:00	13:05	13:10	15:30	Single
0	0:55	7:35	7:40	8:25	8:30	A Trailer Only 
0	2:25	13:05	13:10	13:15	15:30	B-Double
1	0:25	13:05	13:15	13:25	13:30	A Trailer Only 
1	0:25	10:15	10:20	10:30	10:40	Single
1	0:15	15:00	15:05	15:10	15:15	Rigid

venkat1926 · Answer

apparently it looks 
five headings 
Hit and Miss	Time Taken	In time	 Out Time  	Truck Type 
seven items data
0	0:35 	7:00 	7:10 	 7:20	7:35 	 Rigid 

clarify 
otherwise upload sheet  in third party page e.g. speedyshare.com

venkat1926 · Answer

remove the not used items and copy paste back in the message.It is now difficult to copy the data in excel and then again remove or move.

venkat1926 · Answer

download the file from here and see formulas in col. F

http://speedy.sh/46ZSh/nahos-Book1.xls

Nahos · Answer

Pls note that I am using your nahos-Book1 as a reference.

I'm trying to work out if my trailer is running within a certain time frame. E.g. a Rigid should only take 30 mins, a Single 1 hour and a B Double 2 hours. So if for example a Rigid (F2) has taken 35 mins, 5 mins over, then cell A1in will equal 0 but if a Rigid did make it within the allocated time frame then it would = 1 (A10). 

So in column A hndg1 I would really like a formula to distinguish the difference between B Double etc in column E hndg5 and then work out based on the trailer types if they made time constrains.

The time constrains are

Rigid = 30 mins
A Trailer Only = 30 mins
B-Double = 2 hours
Single = 1 hour


Kind Regards Nahos

venkat1926 · Answer

quotge

The time constrains are 

Rigid = 30 mins 
A Trailer Only = 30 mins 
B-Double = 2 hours 
Single = 1 hour 
unquote


where is this information incorporated in the data (A1:E10). how does computer know about this constraint. where do you want me to incorporate this information in the data.;

Josh · Answer

=IF(OR((AND(C2="A Trailer Only",(HOUR(B2)+MINUTE(B2)/60)<0.51)),(AND(C2="B-Double",(HOUR(B2)+MINUTE(B2)/60)<2.01)),(AND(C2="Single",(HOUR(B2)+MINUTE(B2)/60)<1.01)),(AND(C2="Rigid",(HOUR(B2)+(MINUTE(B2)/60))<0.51))),1,0)

I believe that will get you your desired results.  You can place it in the first cell and then drag down to as many as you'd like.

Josh · Answer

=IF(OR((AND(C2="A Trailer Only",(HOUR(B2)+MINUTE(B2)/60)<=0.5)),(AND(C2="B-Double",(HOUR(B2)+MINUTE(B2)/60)<=2)),(AND(C2="Single",(HOUR(B2)+MINUTE(B2)/60)<=1)),(AND(C2="Rigid",(HOUR(B2)+(MINUTE(B2)/60))<=0.5))),1,0)

Sorry for the previous post.  It was premature.  This formula should operate appropriately for you.

If time take is equal to...

7 responses

Similar discussions

Subscribe To Our Newsletter!