Slightly Complex Google Sheets Formula -- 2
Budget: $10 – $30 USD
I need help formatting a slightly complex google sheet. My company has 50-100 projects running at a time and we have limited man power to complete the projects. When I get a contract I am given a date range that the general contractor expects my work to begin and when he expects the project to be complete. So project 1 might be 1/1/22 - 3/31/22 which means I will begin on 1/1/22 and end on 3/31/22. Project 2 could be 2/1/22 - 11/30/22. Project 3 4/1/22 - 8/30/22 and so on.
When I get the project I know that project 1 will take 5 days of crew type 1 & 5 days of crew type 2. Project 2 will take 10 days from crew type 1 & 10 days from crew type 2. Project 3 will take 5 days from crew type 1 and 8 days from crew type 2 and so on.
I would like to be able to put the number of crew days required for each project by crew type, the start and stop dates for the project and have that data push to a projected crew demand by month sheet. In the example above the project is projected to occur in January, February & March and take 5 days of each crew type, so it would push 1.66 days for crew type 1 in January February & March and 1.66 days for Crew Type 2 in January February and March. Project 2 is expected to run from February - November 2022 and requires 10 days from crew type 1 and 10 days from crew type , so it would push 1 day each month for each crew type. I would then total crew types by month to better understand my capacity constraints.
When I get the project I know that project 1 will take 5 days of crew type 1 & 5 days of crew type 2. Project 2 will take 10 days from crew type 1 & 10 days from crew type 2. Project 3 will take 5 days from crew type 1 and 8 days from crew type 2 and so on.
I would like to be able to put the number of crew days required for each project by crew type, the start and stop dates for the project and have that data push to a projected crew demand by month sheet. In the example above the project is projected to occur in January, February & March and take 5 days of each crew type, so it would push 1.66 days for crew type 1 in January February & March and 1.66 days for Crew Type 2 in January February and March. Project 2 is expected to run from February - November 2022 and requires 10 days from crew type 1 and 10 days from crew type , so it would push 1 day each month for each crew type. I would then total crew types by month to better understand my capacity constraints.