Have you ever wondered how to split a task in a conditional format based Gantt Chart in Google Sheets? If a task has been interrupted for some reason, it may be necessary to split the corresponding task bar in the Gantt chart. In this article, I will guide you through the process of splitting a task into two sections in a Gantt Chart in Google Sheets.
Gantt Chart Using Conditional Formatting
Creating a Gantt Chart in Google Sheets is a breeze using conditional formatting. By simply applying a conditional format rule, you can create a simple Gantt chart. In case you missed it, I have already published a post on how to create a Gantt chart using formulas in Google Spreadsheets. But for now, let’s focus on splitting tasks in a Gantt chart.
To split a task in a Gantt chart, we will use the same conditional format rule but in a slightly different way. The rule will automatically be applied to all tasks added to the chart. Before we get into the splitting process, let’s go over the steps to create a Gantt chart using conditional formatting.
Sheet Preparation (Formatting)
In a Gantt chart, you need a task start column, a task end column, and a row with scheduled periods (dates) in sequential order. You can enter the dates manually or use a formula to automatically populate them. For example, if your schedule is based on weeks, you can use the formula =sequence(1,25,date(2020,1,6),7) in cell D2. This formula generates a sequence of dates starting from January 6th, 2020, for a duration of 25 weeks. You can adjust the number of weeks as needed.
Custom Formula
With the Gantt chart set up, it’s time to apply the conditional format rule. Select the range D3:AB14 and open the ‘Conditional format rules’ sidebar panel by clicking Format > Conditional formatting. Insert the formula =and(D$2>=$B3,D$2<=$C3) as per the screenshot provided. This formula will highlight the cells (create bars) based on the task start and end dates. You can add more tasks by entering the task name, start date, and end date in the respective cells.
Now that we have covered the basics of creating a Gantt chart using conditional formatting, let’s move on to the exciting part – splitting tasks in a custom Gantt chart in Google Sheets.
Splitting Tasks in Gantt Chart in Google Sheets
Sometimes, a running task may need to be interrupted for various reasons. In such cases, you can split the task to indicate the interruption. Let’s take an example of splitting “Task 1”.
In the example chart, we want to split the task for three weeks from February 24th, 2020 to March 9th, 2020. To do this, we need to remove the highlighting from the range K4:M4 and extend the bar from P4 to S4 to accommodate the three interrupted weeks.
To remove the highlighting from the bar, follow these steps:
- Create two additional columns. In this example, we will use columns AC and AD.
- Enter the split task start date (February 24th, 2020) in cell AC4 and the split task end date (March 9th, 2020) in cell AD4.
- Copy the earlier formula used for conditional formatting.
- Select the range D3:AB14 and go to the conditional format rules panel.
- Insert the copied formula and change the cell references to match the new split task dates (AC3 and AD3).
- Choose white or light gray as the fill color.
- Make sure the newly created rule is placed on top of the earlier rule.
By following these steps, you can split a task in a custom Gantt Chart in Google Sheets. You can repeat this process for any additional tasks you want to split.
Removing Split in Tasks in Gantt Chart in Google Sheets
To remove split tasks, simply delete the dates in columns AC and AD and restore the original end dates in column C.
And there you have it! You now know how to split tasks in a conditional format based Gantt chart in Google Sheets. It’s a handy technique for visualizing interruptions or changes in your project timeline. For more tips and tricks on Google Sheets, visit Crawlan.com.
