Help with creating a late truckload departure sheet

[deckshermorgan]

Hello all,

This is the first time I've needed to create any kind of formula beyond the basics of SUM, AVG, etc. I need to create a sheet to track late truckload departures.

The above are the column markers and titles for each column. I'm trying to create a sheet whereupon pasting the raw data from the excel sheet will allow me to either highlight or pull the loads that depart late according to our metrics. In the photo above, if 'Status' in column B = 'complete' or 'not in yard', and ADT (actual departure time) in column N is greater than 30 minutes past SDT (scheduled departure time) in column L, then the load would be considered late. The data in column L and column N are formatted as follows: 4/16/2020 12:00, so it includes both the date and the time. I'm not sure if that will complicate things.

​

 

[jasonb75]

it includes both the date and the time. I'm not sure if that will complicate things.

It actually makes it a lot easier.

Try this for row 2, then fill down as needed

=IF(AND(OR(B2="complete",B2="not in yard"),(N2-"00:30")>L2),"Late","Not Late")

 

[Lukasier]

Yeah, I was just writing pretty much the same formula. If you want it to highlight rows, you should put that in conditional formatting