Example1.xls

Hello,

I am working on a project where I need to find the next available train for a trailer to make. I have attached a document that has a column designated for the next available train on the Data sheet and on the next sheet called Rail_Sch I have a sample rail schedule with different days and diffferent times for each of those days that a trailer can make it on. The purpose of this is to compare the First_Avail_Train column to the Rail D Time column to see if the two columns are the same or not and if the First_Avail_Train is before the Rail D Time then I will know that the actual time the trailer got on a train wasnt the first train available.

Ex. On the Data sheet row 2 the "B.A.T DOW" (Best Arrival Time Day of Week) = 21:00 on Tuesday and the ACTUAL time the trailer left on a train is equal to the "Rail D Time"= 5:00 WED. What I need to figure out a formula for is to refer to the Rail Schedule sheet and go to the Tuesday column and slect the time that is the soonest time AFTER the B.A.T DOW and fill in the First_Avail_Train cell with that time from the Rail_Sch. If the next available Train is on a WED (if the B.A.T. DOW is past the last BNSF time on Tuesday then I need it to select the first Train on Wednesday. On Row 2 you will also see on column I either a BNSF or UP and on the Rail Schedule you will also see a column with those. For this example it says BNSF on the Data tab and on the Rail_Sch tab there is both BNSF and UP. I need to first select the BNSF rows and then between those rows select the time that would be closest AFTER the B.A.T DOW time. So the end result needs to have 5:00 Wed in the First_Avail_Train column in row 2.

The day of the week changes for each set of data so I cant just type in tuesday into my formula.

Thanks!