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
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.
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:
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.
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)))’.
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.
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.
5. Select the cell beside the start date, which is D2, and go to Data. After, click on Data validation.
6. In the Criteria, select Custom formula is. Beside that, type in the formula
=and(D2>C2,isdate(D2)). Then, click Save.
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.
8. Then, you will use the
SEQUENCE function to return the inputted dates in the time frame. In E4, input the formula
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.
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.
11. In the menu, type in ‘16’, which will be the width of the cells. Lastly, click OK.
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.
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.
14. So in that merged cell or row 5, we will input the
SPARKLINE function to create the Gantt chart. Input the formula ‘
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.
16. To filter specific dates, simply change the time frame in C2 and D2. And the Gantt chart will automatically filter the specific dates.
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.
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.