I'm having a very difficult time figure out how to allocate hours for projects based on phases. I've attached my spreadsheet for reference.
I'm trying to calculate the number of 'Program' and 'Project' hours I have for the different phases of my project. My phases are:
Phase 1- Strategy Approved- RFA Approved
Phase 2- RFA Approved- Move In
Phase 3- Move In- Decommissioning complete
I want the number of program and project hours to be distributed in the months during the given phase.
For example:
Annapolis Junction has 12 program hours per month in phase 1. It has 0 project hours during phase 1. This phase runs from 6/1/17 through 1/1/18. I want 12 hours entered in columns AC, AE, AG, AI, AK, AM and AO. There are no project hours so nothing is needed there. For phase 2, I need 6 hours entered or program in columns AQ, AS, AU, AW, AY, BA, BC, and BE and 8 hours entered from 1/1/18 through 9/1/18 for project in columns AR, AT, AV, AX, AZ, BB, BD, and BF. For phase 3, I need 4 hours entered for program in column BG and 4 hours entered for project in column BH.
I need a formula that is repeatable for a large number of projects.
I've tried IF formulas, but I just can't figure it out.
Any help would be very much appreciated and will save me literally days of work.
Thank you!
Bookmarks