Have you ever tried applying a date filter in a Gantt Chart in Google Sheets? It can be quite tricky to get the desired results. In this article, we will explore how to properly filter tasks based on specific time frames and create an effective Gantt Chart.
Sparkline Bar to Limit the Gantt Chart View to Certain Dates
Before we dive into the process, let’s understand why we need to use a Sparkline bar in our Gantt Chart. Unlike predefined Gantt chart types in Google Sheets, we can create a customized one using conditional formatting, Sparkline, or Stacked Bar chart. Among these options, Sparkline is ideal for our purpose.
How to Get a Date Filter in a Gantt Chart in Google Sheets
Let’s start with the sample data. Copy-paste the provided data into your Google Sheets document.
Formulas – Duration, Time Frame, and Sparkline
1. Duration
We begin by calculating the duration of each task. In cell B4, insert the following array formula:
=ArrayFormula(if(D4:D="",,days(D4:D,C4:C)))
2. Timescale (Time Frame) for Date Filter in Gantt Chart
The timescale plays a vital role in applying the date filter to our Gantt Chart. In cell C1, enter the start date you want to filter. For now, let’s use “15-11-2021” as an example.
Next, apply a data validation rule to cell D1. Go to Data > Data validation > Criteria > Custom formula is. Insert the following formula and save:
=and(D1>C1,isdate(D1))
This validation ensures that the date in cell D1 is greater than the one in C1. Enter “31-03-2022” in cell D1.
To generate the required time frame, use the following SEQUENCE formula in cell E3:
=sequence(1,60,C1,ROUNDUP(days(D1,C1)/60))
Adjust the width of columns E to BL as per your preference.
3. Sparkline Bar Chart with Date Filter
Select cells E4 to BL4 and go to Format > Merge > Merge horizontally.
Now, let’s create the SPARKLINE function-based formula to draw the Gantt Chart. In cell E4, enter the following formula:
=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})
Copy and paste this formula down to fill the rows.
Congratulations! You have successfully applied a date filter in your Gantt Chart in Google Sheets.
Formula (Sparkline) Explanation
For a better understanding, let’s break down the Sparkline formula:
Generic Formula:
sparkline({task_start-project_start, task_end-task_start},{"charttype","bar";"color1","white";"color2","red";"max",duration})Here are the specific terms used in the formula:
task_start(not C4) =min(max(C4,$C$1),$BL$3)project_start=$C$1task_end(not D4) =max(min(D4,$BL$3),$C$1)task_start=min(max(C4,$C$1),$BL$3)- The total project duration should be
$BL$3-$C$1, not$D$1-$C$1.
If you encounter any difficulties creating a date filter in your Gantt Chart, feel free to use my template.
Thank you for choosing Crawlan.com. Enjoy exploring the world of Gantt Charts in Google Sheets!
Sample_Sheet_19222
