ScienceNerd
New Member
- Joined
- Jun 20, 2019
- Messages
- 2
I am trying to setup an automated scheduling spreadsheet in Excel but have hit a road block.
The actual problem I'm working on is quite complex so as an example imagine a new delivery business (Acme Shipping) that is purchasing trucks and starting delivery routes. As Acme wants to maximize the number of routes it can complete per month. Acme is purchasing new trucks on an irregular basis and wants to keep them all running as much as possible. However, Acme only has a single cleaning/maintenance garage which creates a bottle neck.
For example, Truck A is the first truck purchased and is put into service immediately. It won't be ready for its next route until Jan 9th so Truck B is the next one to start a route, same with Truck C. However, when it comes to the fourth route, it's faster to run Truck A again instead of waiting another day for Truck D.
Lastly, currently all the trucks are running on route Alpha but in the future Acme will have trucks running on route Bravo which takes a day longer.
The goal of the spreadsheet is to quickly forecast which truck will be available future dates to maximize the number of routes completed per month. I've tried using combinations of INDEX, MATCH, and IF functions but nothing seems to be working as I'd like. Any suggestions would be much appreciated!
<tbody>
</tbody>
The actual problem I'm working on is quite complex so as an example imagine a new delivery business (Acme Shipping) that is purchasing trucks and starting delivery routes. As Acme wants to maximize the number of routes it can complete per month. Acme is purchasing new trucks on an irregular basis and wants to keep them all running as much as possible. However, Acme only has a single cleaning/maintenance garage which creates a bottle neck.
For example, Truck A is the first truck purchased and is put into service immediately. It won't be ready for its next route until Jan 9th so Truck B is the next one to start a route, same with Truck C. However, when it comes to the fourth route, it's faster to run Truck A again instead of waiting another day for Truck D.
Lastly, currently all the trucks are running on route Alpha but in the future Acme will have trucks running on route Bravo which takes a day longer.
The goal of the spreadsheet is to quickly forecast which truck will be available future dates to maximize the number of routes completed per month. I've tried using combinations of INDEX, MATCH, and IF functions but nothing seems to be working as I'd like. Any suggestions would be much appreciated!
| days to complete a route Alpha | 5 | Truck | Purchase date | Start | Next availability | Next availability | |
| days to complete route Bravo | 6 | A | 2020-01-01 | 2020-01-01 | 2020-01-09 | 2020-01-17 | |
| cleaning | 1 | B | 2020-01-03 | 2020-01-03 | 2020-01-11 | 2020-01-19 | |
| maintenance | 2 | C | 2020-01-06 | 2020-0106 | 2020-01-14 | 2020-01-22 | |
| D | 2020-01-10 | 2020-01-10 | 2020-01-18 | 2020-01-26 | |||
| Route schedule | Start Route | End Route | Ready for next route | ||||
| Truck | |||||||
| A | 2020-01-01 | 2020-01-06 | 2020-01-09 | ||||
| B | 2020-01-03 | 2020-01-08 | 2020-01-11 | ||||
| C | 2020-01-06 | 2020-01-11 | 2020-01-14 | ||||
| D | 2020-01-10 | 2020-01-15 | 2020-01-18 | ||||
<tbody>
</tbody>
