Vehicle Time Table in Excel to Vehicle Movement Graph in Excel

Good Day,


I work in Public Transport planning, we compile our time tables (origin + origin time and destination + destination time on a specific route / path) in Ms. Excel. Once the timetable is completed it looks something like in the attachment.



You will notice that the timetable is for a specific route (105) and it is for trips in a Forward Direction (F) and return Direction (R) in separate rows etc.


We then "manually move the trips (forward and return) onto a "vehicle movement graph" which looks like the attachment.


The vehicle movement graph is a 24 hour day seperated in minutes from left to right and we then "block" out the time on the graph with the details of the specific trip on the row for a vehicle (each row represents a vehicle). We continue allocating the trips to time blocks, if there is a conflict we add a vehicle to do the trip

For example vehicle 1's time is "blocked" for trip 1 between 5:52 and 6:34 on the forward trip, therefore vehicle 1 cannot do trip 2 between 6:03 and 6:50 on a return trip, therefore vehicle 2 is added.
For trip 3 on the return journey we need a time "block" between 6:21 and 7:07 to operate the trip, both vehicle 1 and vehicle 2 is committed still on their first trips, therefore vehicle 3 has to be added. This continues all the way through.

Another example is trip 6, we need a time "block" between 6:37 and 7:28 on a return journey to operate this trip. The first check is if one of the vehicles already added to the movement graph has the "open time" and secondly it needs to be at "Destination A" at the time to operate the trip back to "Origin A", in the example above, vehicle 1 arrived at the "Destination A" at 6:34 therefore it is available to operate trip 6 back from "Destination A" to "Origin A".

This complete process is currently "manual" on our side. It is a critical process since we need to know how many vehicles are required to operate the time table that has been drawn up - this is the end result, given the time table, X number of vehicles will be required to operate the time table.

The result does not have to be visually as in the example (although it would be nice), it also does not have to be colors etc, the "time blocks" can be indicated as XXXXX etc., it does not matter. I would however run some query / macro or formula to have a "number of vehicles result at the end.

What am I requesting?

I need to have some form of query / macro or formula to have a number of vehicles result at the end to try and remove the "manual" process from moving trips from the timetable to a vehicle graph. We have a large number of route timetables and will have to do all the vehicle determinations "manually". In short:


1. A formula or query to move the trips from the time table to "fit" (in time blocks") onto a line graph indicating which vehicle can fit in which of the timetable trips in its operational movement time would be the best result.
2. If that is not possible, perhaps some formula or query that still follows the process as described and yields an end result for the number of vehicles would be the second best result, HOWEVER we would still have to display the visual graph.

Could someone please assist?