Project Plan Template not taking weekends into account

[darrenj1471]

Hello, I need to create a Project Plan (in excel) and found a neat little template gantt chart. I have made some changes because it vaguely related to Periods and Ive changed these to working days/dates (mon-fri) which seemed ok up until the point when I come to enter the days effort per activity.

Entering a value of 5 days for a task starting on Weds is only showing a 3 day Gantt bar because its assuming task continues into Sat and Sun I assume. My requirement is that it would show the full days running to Tues the following week.

Can someone help me and fix it (or advise me how to if easy) so that the gantt considers only working days when plotting the activity

Ive uploaded my example workbook

Thanks so much in advance

[CAntosh]

Try changing your conditional formatting covering I6:BP34 to:

=AND(I$4>=$C6,NETWORKDAYS($C6,I$4)<=$D6)

This should take care of your planned days. For actual days, change the Cs and Ds to Fs and Gs. Take a look at the attachment to see if it works as desired (I only changed planned days):

[PietBom]

Do you allow me to add 2 columns for Finish dates and adjust the CF formulas ?

[darrenj1471]

Hi

Ive reviewed both and thanks thus far for your help. CAntosh Im afraid I don't understand yours because I open your attachment and see some new cells along top with formulas I don't understand but I don't actually seen any change to the conditional formatting when opening it for whole workbook. I also cannot seem to change conditional formatting at all as I copy what you have provided but when I click apply it doesn't save and reverts back to original value

Pierbom I see you have added a new column for end date and its this end date which appears to be critical in working out the working days?? I think this resolves the issue

[darrenj1471]

see above as realised issue

[darrenj1471]

See above as realised issue

[CAntosh]

Sorry about the formulas at the top of my attachment - they're meaningless. I used them to check my work and should've deleted them before saving. The CF rule I posted should just be used to replace your existing rule for planned dates. I'm not sure why it wouldn't "apply" when you clicked. It seems to work for me? Regardless, I'm glad PietBom's solution gave you one you're comfortable with. Good luck!

[darrenj1471]

Hello again

Sorry to follow up on this but whilst the planning aspect now seems to work fine using PietBoms solution I am having issues with marking tasks complete. Please see the plan Im using and you will note that if you look at Row 8 for example. Its a 5 day task which started on time and is marked as 100% complete however the gant only shows 3 days of 'blue' ie the completion aspect of gant seems to not be considering only working days? Could you maybe help me again?

[PietBom]

Hi darrenj1471,
I understand what you mean and will try to come with a solution this week. (very busy at the moment)

[PietBom]

Made a start with the bars for 100% complete.
More to come.
What methode statement should I use for activities started and in progress (less than 100%) ?

[PietBom]

@darrenj1471,
Can you explain how bars should color when progress is getween 0 and 100% ?