How to Apply Date Filter in Gantt Chart in Google Sheets

This guide will explain how to apply a date filter in the Gantt chart in Google Sheets to identify specific dates in the project timeline easily

In other words, we can select the start date and end date of a specific task using the date filter in Google Sheets. 

Since it helps us visualize the project timeline, deadlines, and status, a Gantt chart is an important tool used in project management. Besides seeing the different tasks and their duration, we can include more features in a Gantt chart depending on our needs. 

For instance, we want to have the ability to filter specific dates in the Gantt chart. This would make it easier when we want to identify tasks and deadlines on specific dates. 

Although Google Sheets does not have a Gantt chart readily available in their chart types, there are still many ways we can manually create a Gantt chart. We can use conditional formatting, a stacked bar chart, or the SPARKLINE function

But this time, we will create a Gantt chart using the SPARKLINE function, allowing us to apply a date filter in the Gantt chart. Because we can’t apply a date filter in a stacked bar chart since it would require continuous editing. 

Also, applying a date filter in conditional formatting would need days as the unit of the timeline. So this would only apply to short-term projects and not long-term projects, which would need months as its unit. 

Let’s take a scenario where you would need a date filter in the Gantt chart. 

Suppose your company has a large-scale project that spans the entire year. And you were tasked to create a Gantt chart for the project. To make it easier for everyone to view their assigned task and duration, you added a date filter.

So they could just filter out a specific date and see the duration of those tasks. This would save time from browsing through the entire Gantt chart just to find those dates.

Great! Let’s move on to a real example of applying a date filter in a Gantt chart in Google Sheets.

 

 

A Real Example of Applying Date Filter in Gantt Chart in Google Sheets

First, let’s take a look at a sample dataset that we will be using to create a Gantt chart. This would usually include the tasks, start dates, and end dates.Sample dataset for a Gantt chart

 

We have to do three main things to create a Gantt chart with a date filter. So the first thing is to calculate the duration of each task. We will be using an ARRAYFORMULA  to do this. 

After calculating the duration, we will calculate the time frame for the date filter. To do this, we need to input a start date. In this case, we will place the start date of the first task. Then, we will apply data validation to the cell next to the start date. 

The data validation ensures that any inputted date in the cell will always be greater than the start date. In this case, let’s place the end date of the project. Then, the SEQUENCE function will be used to filter the specific dates based on the inputted time frame. 

Finally, we will use the SPARKLINE function to create the Gantt chart, and it will look like this:Creating Gantt chart using SPARKLINE

 

The time frame found in B1 is our date filter. So we just need to input the specific dates in columns C and D, and the Gantt chart will filter those dates. 

You can make your own copy of the spreadsheet above using the link attached below. 



How to Apply Date Filter in Gantt Chart in Google Sheets

This section will discuss the process of how to apply a date filter in the Gantt chart in Google Sheets. 

1. First, prepare the dataset. It usually includes the task name, start date, and end date. Also, leave one or two empty columns above the dataset. You will be using this later on.Preparing dataset for Gantt chart

 

2. Second, calculate the duration of each task. So create a column for the duration and input the formula ‘=ARRAYFORMULA(IF(E5:E=””,,DAYS(E5:E,D5:D)))’.Calculating the duration for each task 

Since it is an array, it will automatically apply to the entire column, there is no need to copy or drag it down to the other rows. 

3. If the duration returns in a date format, you just need to change the data format. Select the duration column and go to Formula. Then, go to Number and click on Automatic. Hence, this will return the results as integers. Changing data format from date to integers

 

4. Next, you will use the empty columns above the dataset to create the time frame for the date filter. So input the start date of the first task in C2. In this case, it is 01/06/22.Inputting the start date for time frame

 

5. Select the cell beside the start date, which is D2, and go to Data. After, click on Data validation.Apply Date Filter in Gantt Chart in Google Sheets

 

6. In the Criteria, select Custom formula is. Beside that, type in the formula =and(D2>C2,isdate(D2)). Then, click Save.Apply Date Filter in Gantt Chart in Google Sheets

 

7. Next, input the project’s end date, which is 15/07/22 in D2. The validation will ensure that any date inputted here is greater than the date in C2. Also, this will serve as the time frame for the date filter.Apply Date Filter in Gantt Chart in Google Sheets

 

8. Then, you will use the SEQUENCE function to return the inputted dates in the time frame. In E4, input the formula =SEQUENCE(1,50,C2,ROUNDUP(DAYS(D2,C2)/60))).Apply Date Filter in Gantt Chart in Google Sheets

 

The 50 in the formula will show 50 dates. If you have a longer project, change it to the necessary amount to have enough dates in your Gantt chart.

9. If the SEQUENCE returns as integers instead of a date, go to Format. Then, click on Number and choose the date format you are using in the dataset.Apply Date Filter in Gantt Chart in Google Sheets

 

10. Now it’s time to format the spreadsheet to accommodate the Gantt chart. First, select all the columns that contain dates from the SEQUENCE formula. In this case, select columns F to BC. Then, right-click and click Resize the selected columns.Apply Date Filter in Gantt Chart in Google Sheets

 

11. In the menu, type in ‘16’, which will be the width of the cells. Lastly, click OK.Apply Date Filter in Gantt Chart in Google Sheets

 

12. Next, resize row 4, so the dates will be shown properly. Then, go to Format and click on Rotation. From the drop-down menu, select Rotate up.Apply Date Filter in Gantt Chart in Google Sheets

 

13. Select the first task row. In this case, select F5 to BC5. After, go to Format and click on Merge cells. Then, select Merge horizontally. This will serve as the space for the bars in the Gantt chart.Apply Date Filter in Gantt Chart in Google Sheets

 

14. So in that merged cell or row 5, we will input the SPARKLINE function to create the Gantt chart. Input the formula ‘=SPARKLINE({MIN(MAX(D3,$C$1),$BC$2)-$C$1,MAX(MIN(E3,$BC$2),$C$1)-MIN(MAX(D3,$C$1),$BC$2)},{"charttype","bar";"color1","white";"color2","yellow";"max",$BC$2-$C$1})’.Apply Date Filter in Gantt Chart in Google Sheets

 

This specific SEQUENCE formula not only creates a Gantt chart but also takes into account the time frame, which will be the date filter. Copy the formula or drag it down to the other rows. 

Also, you can choose any color you want the bars to be. In the formula, replace yellow with any color you want. 

15. And this is what it would look like. The time frame in C2 and D2 is essentially the start date and end date of the project, so the Gantt chart shows the duration of all tasks.Apply Date Filter in Gantt Chart in Google Sheets

 

16. To filter specific dates, simply change the time frame in C2 and D2. And the Gantt chart will automatically filter the specific dates.Apply Date Filter in Gantt Chart in Google Sheets

 

For instance, you want to see the task duration from 30/06/22 until 15/07/22. Input those dates in the time frame, and the Gantt chart will filter it out. 

17. Tada! Finally,You have successfully applied a date filter in the Gantt chart in Google Sheets.Apply Date Filter in Gantt Chart in Google Sheets

 

That’s it! Now that you have learned the steps, you can now apply a date filter in your Gantt charts to make it easier to see specific dates, especially in long-term projects. 

Are you interested in learning more about what Google Sheets can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like