Previous Topic Next Topic

If time take is equal to...

Closed
Report
-
 Josh -
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

7 replies

Answer 1 / 7
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
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
Report

Hello,
Yes it has 5 headings. Hit and Miss = 0. Time taken = 0:35. In Time = 7:00. Out Time = 7:35 and the Truck Type = Rigid. The other 2 times are not used = 7:10 and 7:35. Thanks
Report

Hi,

please see below. I hope the example below is easier to export. Thank you for your time.


0 0:35 7:00 7:35 Rigid
0 0:35 12:00 12:35 A Trailer Only
1 0:25 14:10 14:35 B-Double
0 2:30 13:00 15:30 Single
0 0:55 7:35 8:30 A Trailer Only
0 2:25 13:05 15:30 B-Double
1 0:25 13:05 13:30 A Trailer Only
1 0:25 10:15 10:40 Single
1 0:15 15:00 15:15 Rigid
Answer 2 / 7
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
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.
Answer 3 / 7
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
download the file from here and see formulas in col. F

http://speedy.sh/46ZSh/nahos-Book1.xls
Answer 4 / 7

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
Answer 5 / 7
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
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.;
Answer 6 / 7

=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.
Answer 7 / 7

=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.
Ask a question