Trying to add networkdays WITH holidays into an excel Gantt chart

[BigGee]

Hello,
I have an excel spreadsheet with a chart that then feeds into a Gantt chart sheet that utilises Networkdays to exclude weekends in the bar chart section. My formula for this is "=IF(AND(N$2>=$G6,N$2<=$H6),IF(N$5<>"S",$J6/NETWORKDAYS($G6,$H6),0),"")"
However if I add the bank holiday list in where the last 0 is in the formula my bar (which includes a number to indicate the resource required for that particular day) remains unchanged. Can anyone advise if there is a way of doing this?

 

[Fluff]

You need to add the holiday list after the H6 & before the ) not where the 0 is.

 

[BigGee]

Thanks for that, sorry for the obvious mistake. I have corrected that but the "progress" bar (yellow bar) still allocates resource into those holiday days. However it doesnt do it for the weekends. What I want to do is treat those holidays that I have now added in the correct place as it does the weekend days and not include them when it aggregates the time required - is this something you could help with?

 

[Fluff]

Your formula does not enter anything into weekends because of IF(N$5<>"S" which is nothing todo with networkdays.
Do you have actual dates in row 4, or is it just numbers?

 

[BigGee]

Your formula does not enter anything into weekends because of IF(N$5<>"S" which is nothing todo with networkdays.
Do you have actual dates in row 4, or is it just numbers?

Ahh okay. row 4 is a formula
"=Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1)" in the first field and then +1 for each subsequent day.

 

[BigGee]

Your formula does not enter anything into weekends because of IF(N$5<>"S" which is nothing todo with networkdays.
Do you have actual dates in row 4, or is it just numbers?

Could I therefore add an additional IF statement saying IF(N$5<>"H" and annotate the holidays in row 5 as H for holidays and have it work correctly?

 

[Fluff]

That should work.

 

[BigGee]

That should work.

That has fixed it. Thanks for your help!

 

[Fluff]

You're welcome & thanks for the feedback.