A relatively common demand management question I’ve been asked to solve in Excel numerous times is how do I figure out how much time is a person allocated to a project during a certain month?
The answer is relatively simple but requires a visual aid to help figure out the logic of the different scenarios:
As you can see – there are 4 cases that can occur. Our goal is to write an IF statement that models all of these scenarios.
Ta Da! Here you go:
=IF(AND([Project Start]<[Month Start],[Project End]>[Month End]),1,
IF(AND([Project Start]<[Month Start],[Project End]<=[Month End],[Project End]>=[Month Start]),(NETWORKDAYS([Month Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),
IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]>[Month End]),(NETWORKDAYS([Project Start],[Month End])/NETWORKDAYS([Month Start],[Month End])),
IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]<=[Month End]),(NETWORKDAYS([Project Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),0))))
Let's break this down line by line.
=IF(AND([Project Start]<[Month Start],[Project End]>[Month End]),1,
translated is case #1 and it would give us a full 100% allocation during that month.
IF(AND([Project Start]<[Month Start],[Project End]<=[Month End],[Project End]>=[Month Start]),(NETWORKDAYS([Month Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),
translated is case #2 (Project End is either before or equal to Month End), we then find the number of work days between month start & project end and then divide by the number of work days in the month to find the percentage of allocated time.
IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]>[Month End]),(NETWORKDAYS([Project Start],[Month End])/NETWORKDAYS([Month Start],[Month End])),
translated is case #4 (Project Start is after Month Start and before Month End, find the number of work days between Project Start and Month End divided by the total number of work days in the month.)
IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]<=[Month End]),(NETWORKDAYS([Project Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),0))))
translated is case #3 (Project Start is after Month Start and before Month End and Project End is before Month End, find the number of work days between Project Start and Project End divided by the total number of work days in the month.)
Using this formula, you can find (per month, or any other given date range such as an entire year) what percentage of time the project takes up within that date range. This percentage can then be used to calculate number of hours, cost of project, etc.
If you have a specific use case you are interested in getting help with let me know!
~Yosef
If you found this useful, please share!