I have been tasked with completing a report that compares scheduled departure times of the outgoing loads from my facility with the actual departure times of those loads. I have been doing this manually as each load departs, but I have been trying to find a way to automate the entire process so that I can pull the data at the end of the day and get the report immediately. I have been running into issues with this and would appreciate any help or input. The issue I'm running into is that the schedule is wildly inaccurate at times and the destinations are reoccurring. My company wants to ensure that even if there are extra loads generated for a certain destination that we still meet the last scheduled time for that specific destination. Having this specification has made it difficult to return the results I desire. If this is doable then I think I'm on the right track, I'm just missing a formula or two. I have attached a spreadsheet that shows the steps I have made so far and how close I am to my desired results. I am a VERY inexperienced excel user so please be kind. In fact I have not used excel since I took a class to learn the basics back in high school! I would very much appreciate any help regarding this issue. Thanks in Advance.

Attached Files

example.xlsx (14.6 KB, 13 views)