Applying a date filter in a Gantt Chart is tricky in Google Sheets.

You may often require it to see tasks in a defined window of time (time frame). So you will get larger bars due to the shorter period.

Also, it will help you find the tasks starting or in progress from the selected start date or within the defined time frame.

What about using Data > Create a filter to filter the tasks based on the start date?

Related:- Filter by Date Range Using Filter Menu in Google Sheets.

That won’t yield the required output. Because that may hide some tasks (rows).

Can you explain it?

Yep! My source data is in the following format – Task Name (A), Duration (B), Start (C), and End (D).

Assume one of my tasks, i.e., “Drawing Approval” starts on 15-11-2021 and ends on 20-12-2021.

I want to see the tasks in the time frame, i.e., from 15-12-2021 to 14-01-2022.

If I filter column E (Start) for dates >= 15-12-2021, the row containing the task name “Drawing Approval” will become hidden. But I want to see its bar from 15-12-2021.

This post explains how to get a proper date filter in a Gantt Chart in Google Sheets.

Sparkline Bar to Limit the Gantt Chart View to Certain Dates

Since there is no predefined Gantt chart type in Google Sheets, we can create one using conditional formatting, Sparkline, or Stacked Bar chart.

We can rely on Sparkline to get the date filter in a Gantt chart because the other two won’t suit our purpose.

If we use conditional formatting, we may require to use days as the unit in the timescale to get a proper chart. This type is only suitable for a project with a shorter period.

At present, we can’t use the date filter in a Stacked bar Gantt Chart also. Here, we may be required to edit the chart each time to enter the Min value on the X-axis.

How to Get a Date Filter in a Gantt Chart in Google Sheets

We will start with the sample data.

Copy-paste the below sample data in A3:D11 (please refer to screenshot # 1 above).

There are three main steps. They are the formulas for the duration in B4, Timescale in E3, and Gantt Bar in E4:E11.

Formulas – Duration, Time Frame, and Sparkline

1. Duration

Empty the duration column range B4:B and insert the following array formula in cell B4.

=ArrayFormula(if(D4:D="",,days(D4:D,C4:C)))

2. Timescale (Time Frame) for Date Filter in Gantt Chart

It plays a vital role in applying the date filter in the Gantt Chart in Google Sheets.

First, we require to define the window of time to limit the Gantt Chart view.

In cell C1, enter the start date to filter. For the time being, input 15-11-2021 in it.

In cell D1, we will apply a data validation rule.

Go to Data > Data validation > Criteria > Custom formula is. Insert the following formula and Save.

=and(D1>C1,isdate(D1))

The above validation makes sure that the D1 date is greater than the C1 date.

Enter 31-03-2022 in cell D1.

The following SEQUENCE formula in cell E3 will return the required time frame.

=sequence(1,60,C1,ROUNDUP(days(D1,C1)/60))

Select the entire columns E3:BL3.

Right-click and select “Resize columns E – BL” and enter 16 (pixel) as the width of the columns. You can set it as per your requirement.

The above is one of the main formulas in getting the date filter in our Gantt Chart as it dynamically adjusts the timescale units based on the time frame in C1:D1.

Notes:-

This timescale may have a few additional dates in the last few columns.

Because we have predefined 60 columns in E3:BL3 and we have to fill those columns.

So even if your time frame is from a start date to an end date, sometimes, you might see the timescale and bar for a few more days.

I don’t think it is a drawback as it’s negligible.

I have inserted a highlighting rule, i.e., =E$3<=$D$1, in E2:BL2 to draw a line across to show the timeframe limit.

3. Sparkline Bar Chart with Date Filter

Select E4:BL4 and go to Format > Merge > Merge horizontally.

Now here is the SPARKLINE function-based formula to draw the Gantt Chart.

=sparkline(
{min(max(C11,$C$1),$BL$3)-$C$1,max(min(D11,$BL$3),$C$1)-min(max(C11,$C$1),$BL$3)},
{"charttype","bar";"color1","white";"color2","red";"max",$BL$3-$C$1}
)

It will adjust along with the timescale based on the given time frame.

Copy-paste it down.

Follow the above steps to get a date filter in a Gantt Chart in Google Sheets.

Formula (Sparkline) Explanation

Generic Formula:-

sparkline(
{task_start-project_start, task_end-task_start},
{"charttype","bar";"color1","white";"color2","red";"max",duration}
)

task_start (not C4) = min(max(C4,$C$1),$BL$3)

project_start = $C$1

task_end (not D4) = max(min(D4,$BL$3),$C$1)

task_start = min(max(C4,$C$1),$BL$3)

The total project ‘duration’ is not $D$1-$C$1, but $BL$3-$C$1.

If you fail to create a date filter in Gantt Chart as above, feel free to copy my template below.

Thanks for the stay. Enjoy!

Sample_Sheet_19222