Forum Discussion
Gantt Chart without weekends
I have a Gantt chart in Excel to help us with project management and timing. I found an example a few weeks ago and have modified it to get about 95% of the way done.
The issue is weekends. The chart, and the calculations, are using a full 7-day week and not a 5-day work week.
Attached is a screen capture.
L5 contains the formula that starts the chart.
=Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1)
Project_Start is the range T1:AC1, although I'm using it as the end/launch date of the project. My task calculations work back from that date.
Display_Week is the range T1:AC2, again I'm working backwards from the launch date so that's a negative number here.
As outlined in the red box, the tasks and chart are including weekends. How do I fix that so it calculates dates based on work week vs calendar week and also doesn't display weekends on the chart itself?
Thanks!
In columns H and I, change formulas such as
=I9-D9
to
=WORKDAY(I9, -D9)
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
Try this:
Asjut the Start Date Calculation:
=WORKDAY(Project_Start, -7*(Display_Week-1))
Adjust Task Dates:
=WORKDAY(Start_Date, Duration-1)
Modify the Chart to Exclude Weekends:
=IF(OR(WEEKDAY(Date, 2)=6, WEEKDAY(Date, 2)=7), "Weekend", "Weekday")
Thank you HansVogelaar and Kidd_Ip I'm attaching a sample with the recommended changes. L5 is the cell I've been changing.
LMK if you can't access this. Gantt workday example
I get "Access Denied"
HansVogelaarsorry about that, I updated the settings so this should work now. Gantt chart example open access
