Time and Project Management with an Advanced Gantt Chart in Excel

Project Management

Project management Gantt Chart in Excel with custom weekends and holidays.

Project management Gantt Chart in Excel with custom weekends and holidays.

This tutorial shows you how to take the basic Excel Gantt Chart and add conditional formatting so the Gantt chart shows today’s date, custom holidays, and custom weekends. Changing the WORKDAYS.INTL function will also adjust the end date to account for non-working custom weekends and holidays.

A Gantt Chart in Excel is a powerful way to get control of your personal time management or simple business project management. This Gantt Chart in Excel tutorial will show you how to turn the basic scrolling Gantt chart into an Excel Gantt Chart that shows today’s date, custom weekends, and custom holidays using conditional formatting. Project work days automatically adjust to accommodate non-workdays.

Download the Advanced Gantt Chart in Excel tutorial and completed Gantt Chart in Excel. This tutorial includes worksheets used in the basic scrolling Gantt Chart tutorial.

Click here to go to the third tutorial and most advanced Gantt Chart in Excel.

A Gantt Chart in Excel works well to monitor personal tasks and simple project management involving small teams and only a few time dependencies. If you’ve ever been overwhelmed by the software cost or data-entry overhead of full-sized project management software, then you’ll want to use this advanced Gantt Chart in Excel.

Alert! If you need to manage projects that have many dependent tasks, that manage shared resources, or where multiple team members update the project plan, then you should use dedicated project management software.

Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.

Excel Gantt Charts give You Flexibility

One of the advantages of creating your own Gantt chart in Excel is you can customize it to fit your needs. I’ll be showing you additional modifications in the future. To give you a hint at what you can do think of the Gantt chart as just a visual view into a database. The left side with task, lists, days, and dates are just a database. Now think about adding to that database and using Pivot Tables for reporting. As long as interdependent links don’t get to complicated you can do amazing things. I’ll do a few more updates in the future.

If you have modified your Gantt chart and improved its performance, leave a comment and share the idea!

Identifying Custom Non-Working Weekends

Some types of businesses or individuals have non-standard weekends. Using the [weekend] parameter of WORKDAYS.INTL you can adjust the End date for these non-working weekend dates.

The Weekend tab in the tutorial contains a table showing the [weekend] parameters, C10:C23 in the figure below, and their corresponding days of the weeks in D10:E23. Notice that the parameters from 11 to 17 have the first and second weekend date as the same day, e.g., FRI and FRI. This enables you to use a single WORKDAYS.INTL function to calculate the end date no matter whether the custom weekend has one day or two.

Custom weekend table for use with WORKDAYS.INTL Excel function.

Custom weekend table for use with WORKDAYS.INTL Excel function.

The three letter day abbreviations in the weekend table must be the same three-letter abbreviations as used in row 16 of the Gantt Chart. Matching these three-letter abbreviations enables conditional formatting to find and format weekend days.

Cell E9 on the Gantt chart contains the custom weekend parameter. The tutorial shows 2 in cell E9 to indicate a custom weekend of Sunday (SUN) and Monday (MON). E9 is referenced by WORKSDAYS.INTL in the End date calculation so that custom weekends are not included as workdays.

Cells E10:E11 in the Gantt Chart display the three-letter abbreviations for the custom weekend days. This range is named rngWeekends. In the Gantt Chart tab the following formulas retrieve the weekend days from the rngWeekend table,

E10         =INDEX(rngWeekend,$E$9,2)

E11         =INDEX(rngWeekend,$E$9,3)

Conditional formatting uses the three-letter abbreviations in E10:E11 to match against the three-letter abbreviations in row 16. This enables conditional formatting to identify which days are custom weekends.

Identifying Custom Non-Working Holidays

Three year calendar of holidays - 2015-2018.

Three year calendar of holidays – 2015-2018.

No one wants to work on holidays, so let’s remove holidays from available workdays using WORKDAYS.INTL and use conditional formatting to shade holidays on the Gantt chart. To do this the tutorial contains a Holidays tab with a table containing three years of major religious holidays and US national holidays.

The dates in the blue cells in column E of the Holidays tab are what really matter. These holiday dates checked by WORKDAYS.INTL and by conditional formatting in the Gantt chart.

The WORKDAYS.INTL function adjusts its calculations for non-working holidays by including a [Holidays] parameter in all version from Excel 2007 forward.

To add or delete holidays for your business and country, just add or delete rows through the middle of this range shaded in light blue. If you add a holiday with multiple days, enter each day on a separate line. The text information in columns C, D, and F are there for information.

Calculating the End Date and Adjusting for Non-Working Weekends and Holidays

With the two tables above you can specify a custom weekend and a table of non-working holidays. With these and the WORKDAYS.INTL function you can take into account non-work days and forecast forecast the last day each task.

Download the Advanced Gantt Chart in Excel tutorial and completed Gantt Chart in Excel. This tutorial includes worksheets used in the basic scrolling Gantt Chart tutorial.

Click here to go to the third tutorial and most advanced Gantt Chart in Excel.

 

In the calculations used in this Gantt Chart the end date is the first workday after the task is complete.

Alert! Do not use a weekend or custom holiday as the Start day for a project. This throws off the end date calculation and could cause it to be off by one day. I couldn’t figure out how to easily fix this with a very complex set of tables and lookups which would be beyond this tutorial. If anyone has an easy fix I’d love to share it with everyone.

In cell E17 enter the function,

=WORKDAY.INTL($C17,$D17,$E$9,rngHolidays)

Copy that function down the length of your task list.

Here’s what the parameters are doing in this advanced WORKDAY.INTL function,

$C17 Start date. See the Alert! above.
$D17 Task days
$E$9 [weekend] parameter specifying the custom weekend
rngHolidays Range name containing the holiday dates

Formatting the Excel Gantt Chart with Excel’s Conditional Formatting

The interior of the Gantt Chart, from F17:S26, contains no data, but borders and shading show you today’s date, weekends, and non-working holidays. Today’s date line and shading for custom weekends and holidays is accomplished using Excel’s conditional formatting. Excel’s conditional formatting compares the date number or three-letter text abbreviation in row 16, to today’s date, in E6, and the start and end dates of each task. It then uses rules in the conditional formatting engine to draw a border or shade cells.

If you aren’t familiar with conditional formatting check out the basic Gantt Chart in Excel tutorial. The worksheets used in this basic tutorial are included in the advanced Excel tutorial download.

Download the Advanced Gantt Chart in Excel tutorial and completed Gantt Chart in Excel. This tutorial includes worksheets used in the basic scrolling Gantt Chart tutorial.

This figure shows the four conditional formats used in the Advanced Gantt Chart in Excel. Each conditional format has a rule written as a formula. When a cell satisfies that rule (written as a formula) then the formatting is applied to the cell. You can see the beginning of each rule in the area bordered in red. All the rules are explained below.

The order of rules in the Conditional Format Manager makes a difference to final appearance.

The order of rules in the Conditional Format Manager makes a difference to final appearance.

The bottom most rule, with dark blue background, formats the dates from the start of task date through the end of task date. This rule was created in the basic Gantt Chart in Excel tutorial.

To create these conditional formats,

1. Select F17. The conditional formats you create in this cell will later be copied across the range F17:S26.

2. On the Home ribbon, in the Styles group, click the Conditional Formatting tool and select New Rule.

3. In the New Formatting Rule dialog box, select Use a Formula to Determine Which Cells to Format.

4. In the box titled Format Values Where this Formula is True enter the formula,

=F$16=$E$6

This formula tests if the date in F16 is the same as the today’s date in E66. If this is true, then the format you choose in step 5 will be applied. Notice that the formula uses F$16 so that as the formats in cell F16 are copied across the range the formula will always reference row 16.

Project management Gantt Chart in Excel with custom weekends and holidays.

Use conditional formats to make today, holidays, and weekends show in your Gantt Chart in Excel.

5. Click the Format button and apply a red line to the right cell border. Or use any other format you want to mark today’s date. Click Ok.

6. Copy cell E17 containing the new “today” condition across the range F17:S26.

You should see the red line or format you selected appear in the cell with today’s date.

Using Conditional Formatting to Mark Custom Holidays in the Gantt Chart

Create a conditional format to shade cells in custom holidays. The test for whether a date is a custom holiday is done by matching the date in row 16 against the dates in range rngHolidays. rngHolidays is the light blue range containing dates in the Holidays tab.

Download the Advanced Gantt Chart in Excel tutorial and completed Gantt Chart in Excel. This tutorial includes worksheets used in the basic scrolling Gantt Chart tutorial.

Select cell F17 and use the same process as when you created the conditional format above, but this time enter the formula,

=IF(ISNA(MATCH(F$16,rngHolidays,0)),””,TRUE)

This formula checks to see if the date in F$16 is in the custom holiday range, rngHolidays. MATCH looks for an exact match as specified by the 0 parameter. If there is no match, an NA error is returned so the IF function returns “”. If the date in row 16 is in the custom holidays list, then TRUE is returned and the conditional formatting applies the custom format for holidays. The template uses a light grey for custom holidays.

Copy cell F17 across the range F17:S26 to see the custom holiday formatting.

Using Conditional Formatting to Mark Custom Weekends in the Gantt Chart

Creating a conditional format to check for custom weekends is a little more tricky because the conditional weekends are listed as text in the Weekend tab, but the dates in row 16 are in date format. There are many possible ways to solve this, but this method uses the three-letter abbreviations in the custom weekend table and matches them to the three-letter custom date formats in row 16. (If you aren’t familiar with the way Excel stores dates, Excel stores the date as a number in the cell, but the way it appears, in this case as three letters, depends upon formatting.)

Enter the conditional formatting for custom weekends as you did the other conditional formats. Select cell F17 and enter this formula as the conditional rule,

=OR(TEXT(F$16,”ddd”)=$E$10,TEXT(F$16,”ddd”)=$E$11)

When this rule is TRUE the conditional format is applied. It is TRUE when the three-letter date abbreviation in row 16 matches either three-letter abbreviation in E10 or E11. The dates in E10 and E11 are extracted from the custom weekend table when you enter the [weekend] parameter in cell E9.

Copy cell F17 across the range F17:S26 to see the custom holiday formatting.

Adjust the Order in Which Conditional Formats are Applied

Alert! Conditional formats are applied in layers, so the order in which Excel checks rules makes a difference as to how the formats layer on top one another. If you find your Gantt Chart doesn’t appear correctly, check the order of rules in the Rules Manager. Reorder rules by selecting a rule and then moving it up or down with the up or down arrow buttons in the Rules Manager.

Put your rules in the same order as the figure below if you want your conditional formatting to appear like that in the tutorial.

Use the up or down arrows to change the order of rules.

Use the up or down arrows to change the order of rules.

Expanding Your Gantt Chart in Excel

This tutorial has only 10 rows of tasks. A more realistic Gantt Chart in Excel might have 25 to 100 tasks. You can expand this Gantt chart to include as many tasks and dates as you need.

To add additional rows to your Gantt chart, select the last row of the Gantt chart and drag down as many rows as you need. Now, change the task titles, start and end dates.

To expand the Gantt chart farther to the right so you can see a wider range of dates, select from S14 downward and drag to the right. This copies to the right the date cell formulas in the top three rows and the conditional formatting in the Gantt chart.

If you want to scroll through more than 100 days, then you will need to change the properties of the scroll-bar by right-clicking and choosing Format Control.

To make your Gantt chart scrollable left and right or up and down while still being able to see titles and the dates in rows 14:16,

1. Select cell F17.

2. On the View ribbon, in the Window group, select Freeze Panes.

This freezes the left and top date titles while allowing the Gantt chart to scroll left or right, up or down.

 

I hope you enjoy this puppy. Using a similar Excel Gantt Chart I’ve managed projects that spanned four months, involved 12 people, and included about 25 major tasks with multiple sub-tasks. It even generated management reports. I’ll show you how to add more capabilities in the future.

If you have more ideas on how others can improve this, please add a comment.

Signup for the Critical to Success newsletter and get more Excel and professional productivity tips each week.

Download the second tutorial of Advanced Gantt Chart in Excel. This tutorial includes worksheets used in the basic scrolling Gantt Chart tutorial.

Click here to go to the third tutorial and Most Advanced Gantt Chart in Excel.

 

Share the power...

Leave a Comment:

Leave a Comment: