We can highlight the remaining days in three types of Gantt charts in Google Sheets. First, let’s understand our options.

As far as I know, we can use highlight rules (conditional formatting), a Stacked Bar chart, or the SPARKLINE function.

When preparing Gantt charts, we usually use two colors in spreadsheets:

• Project start to task start: White color (sheet’s background color) to render the bar invisible up to that point.
• Task start to task end: Any other color.

However, to display the remaining days in a Gantt chart, we require three colors:

• Project start to task start: White color.
• Task start to elapsed days (days passed): Light orange (or any color other than white).
• Remaining days: Red (or any color other than the previous two).

For SPARKLINE, a different style of data formatting is required, including duration and remaining days calculation. We will cover that in another tutorial. Here, we will focus on the other two options.

Remaining Days in Gantt Charts – Explained

To find the remaining days, we require three parameters: the task start date, the end date, and today’s date. The end date serves as the task deadline.

We need to calculate the number of days from today to the task end date. This gives us the days remaining until the deadline.

For example, the scheduled duration of the activity “Panel erection” is from 26-01-2022 to 28-02-2022.

If today’s date is 14-02-2022, the remaining days to complete the “Panel erection” is 14, the days elapsed/passed is 19, and the total duration is 33 days.

Data Preparation: Elapsed Days, Remaining Days, and Duration

We require a couple of formulas to achieve our goal. I have explained them below.

The first three columns contain task names, task start dates, and end dates.

We can use formulas based on the MAX, MIN, and TODAY functions to get the values in the remaining columns.

Here they are:

• D3 – Returns the Number of Days Elapsed:

=MAX(0, DAYS(MIN(TODAY(), C3), B3))

• E3 – Returns the Remaining Days (to reach the deadline):

=MAX(0, C3-MAX(TODAY(), B3))

• F3 – Duration:

=DAYS(C3, B3)

You should copy these formulas from the respective cells and paste them into cells down to row 10.

The above are the essential data formatting steps to highlight the remaining days in a Gantt Chart in Google Sheets.

Note:

I’ll share my sample sheet with you at the end of this tutorial. In it, you may see formulas in B3:B10 (under project start and end dates) to make the sample data evergreen. You can ignore them completely.

Using Stacked Bar Charts to Highlight Remaining Days in Gantt Charts in Google Sheets

Here I’ll share only the necessary settings with you.

You can refer to my sample sheet at the end of this tutorial to resolve any issues you may encounter along the way.

Let’s explore how to utilize the Stacked Bar graph to obtain or showcase the remaining days in a Gantt chart in Google Sheets.

Required Stacked Bar Graph Settings

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

Select range A2:E10 and go to Insert > Chart.

Follow the settings below under the Chart editor > Setup tab:

1. Chart type: Stacked bar chart.
2. Y-axis: “Task.”
3. Series: “Project Start,” “Elapsed Days,” and “Remaining Days.” If additional series are present, remove them.

Now, under the Chart editor > Customize tab:

1. Under Series, set the fill colors for “Project Start,” “Elapsed Days,” and “Remaining Days” to White, Light Orange, and Red, respectively.
2. Enable “Data labels” for each series. Click on Number format > Other custom formats and enter "deadline" #0 "days" in the provided field. Click “Apply.” (This step is optional.)
3. Under the Horizontal axis, enter the date value from cell A1, such as 44510 (or whatever value you get) based on the sample above, into the “Min” field.
4. Check “Allow boundaries to hide data.”
5. Click on Number format and select “Date and Time.”

The above settings represent one of the options to display the remaining days in a Gantt Chart in Google Sheets.

Highlighting Remaining Days in Gantt Charts with Conditional Formatting

If you are familiar with conditional formatting, you can follow the method below to highlight the remaining days in a Gantt chart in Google Sheets.

However, the “Data Preparation” provided above is not sufficient. We also need a timescale at the top of the bar area, which should be in cells G2:AF2.

In cell G2, insert =MIN(B3:B10).

In cell H2, insert =G2+7 and then copy-paste across cells I2:AF2.

You will obtain a timescale as shown below.

Note: Since the timescale includes weeks (7 days), tasks with shorter durations might not always be visible.

Now, we need to insert two custom rules within Conditional Formatting. Here they are:

Rule 1 (to highlight the remaining days in the Gantt chart bar):

=ISBETWEEN(G$2, MAX(TODAY(), $B3), $C3)

Rule 2 (to display the bar):

=ISBETWEEN(G$2, $B3, $C3)

How to Apply Them:

1. Select range G3:AF10.
2. Go to Format > Conditional formatting.
3. Under Format rules, select “Custom formula is”.
4. Insert the Rule 1 formula, set the fill color to Red, and click “Done”.
5. Insert the Rule 2 formula, set the fill color to Light Orange, and click “Done”.

Sample Charts 15222

Resources

• Task Duration, Remaining, and Elapsed Days Calculation: Google Sheets
• Track Remaining Days in Tasks with Sparkline Gantt Charts
• Create Gantt Chart Using Formulas in Google Sheets
• Basic GANTT Chart in Google Sheets Using Stacked Bar Chart
• Create a Gantt Chart Using Sparkline in Google Sheets
• Split a Task in a Custom Gantt Chart in Google Sheets
• Multi-Color Gantt Chart in Google Sheets
• Date Filter in Gantt Chart in Google Sheets
• GANTT_CHART Function in Google Sheets (Named Function)