Forum Discussion
Show correct %-Allocation in Resource Usage for a specific project when using Resource Pool
The Excel function NETWORKDAYS will not give the correct result for a couple of reasons. Although the function includes a argument for holidays there is no field in Project that provides Project calendar information. Second, a given resource may also have, in addition to holidays, time off for vacations, etc. Project does however provide the Work Availability timescaled field. From Work and Work Availability, Percent Allocation can be calculated for each timescaled period.
Project's Visual Reports feature does provide an export to Excel of Resource Usage information including a Work Availability report, but, the data is total resource data, not broken down by project and there is no way to include the Project field in the cube data.
I'm not sure what you mean by "share the Excel file for this scenario". As I noted the existing macro I have is not set up to break down the data by Project. In its current state, this is what the Excel export looks like. It was written and optimized for another user's master file structure. Modification is needed to produce the report format you want.
John
- Thanks, I thought you were willing to share the macro if it is broken down by project it would be great, if not you can please share it, I am not a VBA expert, but I will see what I can do.
You are correct about NETWORKDAYS, it is a simplification, but INMO there is no other way to do it, because MS Project as we know now, doesn't calculate %-Allocation in certain scenarios, so we can not rely on this calculation. The only calculation we know is correct by projects is Work, so we can calculate a specific allocation, say %-Theoretical Allocation, since it doesn't consider the particular resource constraints such as vacation, local holidays, etc. That is what I meant. Export to Excel will do one portion of the work, exporting hours, then find a way to deduce from the hours %-Allocation. It is a pitty that MS Project doesn't provide %-Allocation in all cases. Thanks so much for your help.- davidleal,
I took a further look at my macro code and I see how I could modify it to produce the format you want. If you're interested in that let me know via e-mail. Note, I will ask some questions and it's not something you will get right away.
John
jmacprojataticlouddotdotcom
(remove obvious redundancies)