I'm not sure where to start on this project, hoping all you excel geniuses will be able to point me in the right direction!
I have 400+ bus routes that go to either a middle school (MS) and an elementary school (ES) or a high school (HS) and an elementary school (ES). I bill based on the time the route takes. I have worked my master spreadsheet so that I can easily bill when all school are in session every billable day of the month. My problem arises when one type of school's schedule changes.
This month, there are 17 billable days for HS and ES. There are 16 billable days for MS. There was 1 day where ES got out early and another day where MS got out early. When ES gets out early, billing begins at 1 pm for all routes that have an ES. When the MS gets out early billing begins at 11 AM for all routes that have a MS.
Does anyone have any suggestions on how I can account for these exceptions? I know nothing about VBA, so I would prefer not to use it. If you need more information I can certainly give more detail. I have attached a sample spreadsheet of the data I have to work with.
Bookmarks