MS Project - adding an exception to a resource calendar for a working day on a defined non-working day throws out % Complete value

Question

I originally asked this question in the 'main' site (link) but given that the behaviour seems to be inherent in MS Project and not to do with using VBA I hoped someone here might be able to advise a workaround if they have encountered the same thing. The same behaviour occurs when performing the same actions via the UI rather than VBA.

I have written a macro that reads in timesheet data and updates the TimeScaleData in the project to update the status. The macro works well overall but I've encountered an issue when one staff member has an arrangement to work on the occasional Saturday or a Sunday, which are defined as a non-working days in the base calendar. The macro successfully adds an exception to the resource's calendar for a working day but it throws out the % Complete calculations.

You can see the effect this has in this screenshot: The Status Date is the 17th but the % Complete bar in the centre of the task bar is showing approximately 1.5 working days after the status date.

As expected, this doesn't occur when I add the exception to the base calendar but it then changes that weekend to a working day for all resources.

I'm not sure if this is something that can be remedied or a result of the way project calculates % Complete across a task with several resources.

Any advice will be appreciated.

Thanks in advance!

Answer 1

This behavior can be tricky, and it's not related to VBA, as you've mentioned it occurs even when using the UI.

Unfortunately, there might not be a straightforward solution to this, but I can suggest a workaround you might consider trying:

Split the Task:

You can split the task into two parts: one before the exception and one after. This way, you can manually adjust the % Complete for each part to more accurately represent the progress made.

Use Milestones:

Instead of relying solely on the % Complete bar, you could add milestone markers to represent key progress points. Milestones won't be affected by the calendar exception, and they can give a clearer picture of task progress.

Adjust Task Dates:

If possible, you could adjust the task start and end dates to exclude the working day exception. This might require some manual adjustments to keep the task duration accurate.

Remember to Use Workarounds

Remember that Microsoft Project's behavior might be due to how it calculates % Complete across tasks with multiple resources and exceptions. Since your macro works well overall, it's likely a specific interaction that's causing this issue.

It's a good idea to consider reaching out to Microsoft support or community forums to see if others have encountered a similar issue and found effective workarounds. The Microsoft Project user community might have insights or tips that can help you address this problem more effectively.