In this post, let’s learn to create a flexible timescale for a Gantt Chart in Google Sheets. The approach is like below.
Using a drop-down, you can switch between different time units. It includes Hours, Days, Weeks, Fortnights, Months, Quarters, Half Years, and Years.
Imagine you want to show the Gantt bars across weeks, not days, then you may pick the required unit from the drop-down. The custom formula will adjust the timescale units accordingly.
I’ll also share the Gantt Chart formula with you. But do remember that shorter time units are preferable.
In larger units, bars for shorter tasks may not appear.
Let’s see how to create a flexible timescale for Gantt Chart in Google Sheets.
Creating a Drop-Down That Contains Time Units
My formula will support the following units.
Hours,Days,Weeks,Fortnights,Months,Quarters,Half Years,YearsWe will first create a drop-down in cell B1 for selecting the units.
- Copy the above units.
- Go to Data > Data Validation.
- Cell range: B1.
- Criteria: List of Items. Insert the copied units.
- Click Save.
Later, our flexible timescale formula in cell E3 will generate the unit for the Gantt Chart across row#3 (E3:3) based on the value selected in cell B1.
Other Inputs for Generating Flexible Timescale for the Gantt Chart
We should also provide the number of units required in cell B2 and the project starting date (DateTime format) in cell B3.
For testing, you may enter 14 in cell B2 and the DateTime 01/07/2021 00:00:00 in cell B3.
In cell B3, include the time component as one of our units in our flexible timescale is “Hours.”
Note 1:-
To dynamically get the number of units required (here, the value in cell B2), we may find the difference between the project start date and the project end date.
But the issue while doing so is, we must consider the unit in cell B1.
Right now, we will manually input the B2 value. But, later on, of course, I’ll provide that formula also.
Above, we have completed the following three essential steps to generate the flexible timescale for the Gantt chart in Google Sheets.
- Created a drop-down in cell B1.
- Entered the number of units to generate in cell B2.
- Inserted the project start date (in timestamp format) in cell B3.
Flexible Timescale Formula for Gantt Chart in Google Sheets
Empty the row range E3:3 and in cell E3, insert the below formula and then format the range E3:3 to Date from Format > Number > Date.
=ArrayFormula(
if(B1="Hours",TIME(SEQUENCE(1,B2,hour(B3),1),0,0),
ifna(
edate(B3,
sequence(1,B2,0,
ifs(B1="Months",1,B1="Quarters",3,B1="Half Years",6,B1="Years",12)
)
),
sequence(1,B2,B3,ifs(B1="Fortnights",14,B1="Weeks",7,B1="Days",1))
)
)
)Note 2:-
If the unit is Hours, format the range E3:3 to Time (Format > Number > Time).
Now change the unit in cell B1. For example, select “Half Years” in cell B1. Then enter 4 in cell B2.
The formula will generate the dates 01-Jul-2021, 01-Jan-2022, 01-Jul-2022, and 01-Jan-2023 in cell range E3:H3.
Enter 01/01/2021 00:00:00 in cell B3. Then the formula will return 01-Jan-2021, 01-Jul-2021, 01-Jan-2022, and 01-Jul-2022.
Can you explain the above formula, which acts as a flexible timescale for the Gantt chart in Google Sheets?
Formula Explanation
We can split the formula into three parts. They are IF, EDATE, and IFNA.
Part 1 – IF (Hours)
It’s for the unit “Hours.”
if(B1="Hours",TIME(SEQUENCE(1,B2,hour(B3),1),0,0)The IF statement is for testing the value in cell B1.
If it evaluates to TRUE, i.e., if(B1="Hours", the formula will return “Hourly” time units.
Similar:- How to Increment Time By Minutes and Hours in Google Sheets (Using ROW instead of SEQUENCE).
Part 2 – EDATE (Months, Quarters, Half Years, and Years)
This part also plays a vital role in our flexible timescale formula.
When the value in cell B1 is not “Hours,” the above part 1 formula may return FALSE.
Syntax:- IF(logical_expression, value_if_true, value_if_false)
That means B1="Hours" is the logical_expression, and part 1 formula is the value_if_true argument.
If part 1 evaluates to FALSE, in value_if_false, the IF will execute the EDATE part 2.
edate(B3,
sequence(1,B2,0,
ifs(B1="Months",1,B1="Quarters",3,B1="Half Years",6,B1="Years",12)
)
)Syntax:- EDATE(start_date, [months])
Using the EDATE function, we can get a date that is a specified number of months (months) after another date (start_date).
For example, the formula =edate("01/01/2021",5) will return 01/06/2021.
Must Read:- How to Utilise Google Sheets Date Functions [Complete Guide].
EDATE Explained:-
start_date = B3 (project start date).
months = SEQUENCE formula.
We will specify months using the SEQUENCE function so that the EDATE will return multiple dates.
Syntax:- SEQUENCE(rows, [columns], [start], [step])
rows = 1 (we want to generate the sequence in one row).
column = B2 (we want B2 number of sequences).
start = 0.
step = The IFS logical test in part 2 formula returns 1, 3, 6, or 12 based on the unit selected.
If B2 = 3 and IFS evaluates 6, we can translate the above formula into;
edate(B3,
sequence(1,3,0,
6
)
)The output of the Sequence will be {0,6,12} and that is the months in EDATE.
Similar:- How to Get Sequence of Months in Google Sheets.
Part 3 – IFNA (Fortnights, Weeks, and Dates)
If the IFS within EDATE doesn’t match any of the units, it will return N/A.
That means the selected unit is not Months, Quarters, Half Years, or Years.
In that case, the IFNA function will execute the below part.
sequence(1,B2,B3,ifs(B1="Fortnights",14,B1="Weeks",7,B1="Days",1)It generates dates in 1 row, B2 number of columns, starting from B3 based on the step value returned by the IFS.
That’s the break up of my flexible timescale formula for Google Sheets.
Flexible Timescale and How the Gantt Chart Responds to It
A formula-based Gantt Chart will respond to our flexible timescale in Google Sheets.
As a side note, you can also use Stacked Bar Chart or the Sparkline function to plot the Gantt chart in Google Sheets.
We have already written the formula for generating the flexible timescale for Gantt Chart. It’s in cell E2, and it requires the input in cells B1, B2, and B3.
For the Gantt chart, we require the following additional inputs.
B4: Project End Date (Timestamp format).
B5:B10 – Task start dates.
C5:C10 – Task end dates.
Now select the range E5:10. Within the Conditional format, insert the following custom formula.
=and(E$3>=$B5,E$3<=$C5) That will generate the bar based on the timescale unit chosen.
Note 3:-
If the timescale unit is “Hour,” enter the start time and end time of the tasks in B5:B10 and C5:C10, respectively.
It should be in HH:MM:SS format like 07:00:00.
Additional Formula
In cell C2 (please refer to the above screenshot), you can see one value that I haven’t used in any formulas.
It actually returns the number of units based on the B3:B4 project start and end dates and the timescale units selected in the drop-down.
I have used the below formula in cell C2.
=ifs(B1="Days",datedif(B3,B4,"d")+1,B1="Months",datedif(B3,B4,"m")+1,B1="years",datedif(B3,B4,"y")+2,B1="Half Years",roundup((datedif(B3,B4,"d")+1)/182)+1,B1="Quarters",roundup((datedif(B3,B4,"d")+1)/120),B1="Fortnights",roundup((datedif(B3,B4,"d")+1)/14),B1="Weeks",roundup((datedif(B3,B4,"d")+1)/7),B1="Hours",roundup((B4-B3)*24))You can either use it in cell B2 or refer it to hand-enter the number of units you want in cell B2.
That’s all about how to create a flexible timescale for the Gantt chart in Google Sheets.
Thanks for the stay. Enjoy.
Related:-
