Days Remaining in Gantt Chart: Highlighting Your Path to Success

Understanding the Options: Highlighting Rules, Stacked Bar Charts, and SPARKLINE Function

Before we dive into creating our Gantt chart, let’s explore the available options. Unfortunately, Google Sheets does not have a native Gantt chart feature in the Insert > Chart menu. However, we can rely on highlighting rules, stacked bar charts, or the SPARKLINE function to get the job done. Out of these three methods, the SPARKLINE function falls short as it only supports two colors. To effectively represent the remaining days in our Gantt chart, we need three colors. As a result, we will set aside the SPARKLINE function and focus on the other two options.

Days Remaining in Gantt Chart – What’s It All About?

To find the days remaining for each task, we need three parameters: the task start date, the task end date, and today’s date. By subtracting today’s date from the task end date, we can determine the number of days remaining until the deadline. For example, if the scheduled duration of the activity “Panel erection” is from 26-01-2022 to 28-02-2022, and today’s date is 14-02-2022, then the remaining days to complete the task would be 14, with 19 days already elapsed and a total duration of 33 days.

Data Preparation: Elapsed Days, Remaining Days, and Duration

To achieve our goal, we need a few formulas. Don’t worry; we’ll guide you through them! First, make sure to input the project start date, task start date, and task end date in columns A, B, and C, respectively. Then, use the following formulas:

D3: Returns the number of days elapsed.
=MAX(0, DAYS(MIN(TODAY(), C3), B3))

E3: Returns the remaining days until the deadline.
=MAX(0, C3 - MAX(TODAY(), B3))

F3: Represents the duration of the task.
=DAYS(C3:C, B3:B)

Copy and paste these formulas up to row #10 to complete the data formatting required for highlighting the days remaining in your Gantt chart.

Using Stacked Bar Charts to Get Days Remaining in Your Gantt Chart

Now that we’ve prepared the necessary data, let’s explore how to utilize stacked bar charts to display the days remaining in your Gantt chart. Follow these steps:

In cell A1 or any blank cell, insert =DATEVALUE(MIN(B3:B10)). This formula will return the date value of the project start date, which we will use later.

Select cells A2:E10 and navigate to Insert > Chart.

Configure the following settings in the Chart editor > Setup tab:

Chart type: Stacked bar chart
Y-axis: “Task”
Series: “Project Start,” “Elapsed Days,” and “Remaining Days”

Adjust the following settings in the Chart editor > Customize tab:

Under Series, set the fill colors for “Project Start” to white, “Elapsed Days” to light orange, and “Remaining Days” to dark red.
Optionally, you can add data labels and customize their number format to display the deadline in the format “deadline”#0 “days”.
Under the Horizontal axis, enter the date value from cell A1 in the “Min” field.
Select “Allow boundaries to hide data.”
Choose the “Date and Time” number format.

By following these steps, you will be able to visualize the days remaining in your Gantt chart using stacked bar charts. Your project management just got a whole lot easier!

Highlight Days Remaining in Your Gantt Chart Using Conditional Formatting

If you’re comfortable with conditional formatting, we have another option for you! This method involves the use of a timescale at the top of the bar area. Here’s how you can do it:

Start by preparing your data as explained earlier.

In cells G2 to AF2, create a timescale by inserting the dates corresponding to each column.

To apply the conditional formatting, you will need two custom rules. Enter the following formulas:

Rule 1: Highlight the days remaining in the Gantt chart bar.
=ISBETWEEN(G$2, MAX(TODAY(), $B3), $C3)
Rule 2: Create the bar.
=ISBETWEEN(G$2, $B3, $C3)

Select cells G3:AF10 and go to Format > Conditional formatting.

Configure the format rules as follows:

For Rule 1, set the fill color to dark red.
For Rule 2, set the fill color to light orange.

Make sure that the dark red rule is above the light orange rule by dragging and rearranging the rules accordingly.

With these steps, you can highlight the days remaining in your Gantt chart using conditional formatting. Stay organized and stay on top of your project’s progress!

Thank you for joining us on this journey to master Google Sheets and create impactful Gantt charts. To explore more tips and tricks, visit Crawlan.com and unleash the full potential of spreadsheet magic. Happy charting!