ozmystichrome
New Member
- Joined
- Jul 29, 2017
- Messages
- 3
Hi All,
I was a member here several years ago but long since forgot my details so created a new user account (apologies for my first post being a pain)...
I'm currently putting together a workbook to allocate drivers to trucks on any given day, each with multiple jobs on most days.
Each job booked will have a start time and a finish time. Drivers / trucks will then be allocated to the jobs for the day. There will be multiple jobs which exist within the same time period. So far so drama.
The question now posed is can I provide a separate worksheet (we'll call it "Availability") that depicts when drivers are allocated to jobs and when they are available (we'll call it "Busy" / "Free").
The format requested is:
- Driver names in Col A
- Time periods (1 hour increments over a 24 hour period) in Row1
- Cell A1 to be a date value (entered by the user) which then generates results based on the data in the Allocation sheet
The objective is to provide "Busy" / "Free" values in each time period for each driver based on start and finish times booked for each job in the Allocation sheet. These values I will colour code for visual reference.
I have seen (and used) multiple examples or array formulas where multiple values are listed one after the other but this isn't what I'm trying to achieve.
The simple syntax for what I am trying to do is:
- In the Allocation sheet, for any given "Date" is a driver allocated to any jobs? Y/N
- If Y, then look up all periods the driver is allocated for and mark the corresponding cells in the "Availability" sheet
- Repeat for every driver
I can generate the Yes/No result for time period cells when I point to a specific job but haven't been able to add the logic to search for multiple occurrences of multiple drivers within 1 day, and then take each occurrence and return the "Busy" /" Free" result to every hour. Just too many variables for me to get it straight.
Example:
- On the Allocation Sheet for 24 July I have booked a job for Fred from 05:00 to 11:00 and a second job for Fred from 15:00 to 18:00
- On the Availability Sheet when I enter 24 July as the analysis date, against Fred's name each hour cell for the above time periods would state "Busy" and the remaining cells would state "Free"
- This would repeat for every driver allocated within that day
- Looking at the Availability Sheet I would be able to determine which hours Fred was still "Free" in case I needed to send him another job
- I can also see who I haven't allocated anything to
Finally, and as a multiple pain, I would like to return a "Doubled Up" value if 2 jobs overlap as Fred can't be in 2 places at once
For the purposes of identification, in the Allocation Sheet:
- Col C = Date
- Col AD = Driver Name
- Col AH = Job Start Time
- Col AI = Job End Time
Looking forward to your kind assistance.
Thanks,
Oz
I was a member here several years ago but long since forgot my details so created a new user account (apologies for my first post being a pain)...
I'm currently putting together a workbook to allocate drivers to trucks on any given day, each with multiple jobs on most days.
Each job booked will have a start time and a finish time. Drivers / trucks will then be allocated to the jobs for the day. There will be multiple jobs which exist within the same time period. So far so drama.
The question now posed is can I provide a separate worksheet (we'll call it "Availability") that depicts when drivers are allocated to jobs and when they are available (we'll call it "Busy" / "Free").
The format requested is:
- Driver names in Col A
- Time periods (1 hour increments over a 24 hour period) in Row1
- Cell A1 to be a date value (entered by the user) which then generates results based on the data in the Allocation sheet
The objective is to provide "Busy" / "Free" values in each time period for each driver based on start and finish times booked for each job in the Allocation sheet. These values I will colour code for visual reference.
I have seen (and used) multiple examples or array formulas where multiple values are listed one after the other but this isn't what I'm trying to achieve.
The simple syntax for what I am trying to do is:
- In the Allocation sheet, for any given "Date" is a driver allocated to any jobs? Y/N
- If Y, then look up all periods the driver is allocated for and mark the corresponding cells in the "Availability" sheet
- Repeat for every driver
I can generate the Yes/No result for time period cells when I point to a specific job but haven't been able to add the logic to search for multiple occurrences of multiple drivers within 1 day, and then take each occurrence and return the "Busy" /" Free" result to every hour. Just too many variables for me to get it straight.
Example:
- On the Allocation Sheet for 24 July I have booked a job for Fred from 05:00 to 11:00 and a second job for Fred from 15:00 to 18:00
- On the Availability Sheet when I enter 24 July as the analysis date, against Fred's name each hour cell for the above time periods would state "Busy" and the remaining cells would state "Free"
- This would repeat for every driver allocated within that day
- Looking at the Availability Sheet I would be able to determine which hours Fred was still "Free" in case I needed to send him another job
- I can also see who I haven't allocated anything to
Finally, and as a multiple pain, I would like to return a "Doubled Up" value if 2 jobs overlap as Fred can't be in 2 places at once
For the purposes of identification, in the Allocation Sheet:
- Col C = Date
- Col AD = Driver Name
- Col AH = Job Start Time
- Col AI = Job End Time
Looking forward to your kind assistance.
Thanks,
Oz
