Timeline charts in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to calculate the duration between start and end dates in days.

Google Sheets

=DATEDIF(A2, [1], "D")

Drag options to blanks, or click blank then click option'

AC2

BB2

CA3

DD2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the end date.

Forgetting to put "D" for days in the formula.

The DATEDIF function calculates the difference between two dates. Here, A2 is the start date and B2 is the end date. The "D" means the difference is in days.

2fill in blank

medium

Complete the formula to create a timeline bar length by multiplying duration by a scale factor in cell D1.

Google Sheets

=DATEDIF(A2, B2, "D") * [1]

Drag options to blanks, or click blank then click option'

AC2

BA1

CD1

DB1

Attempts:

3 left

💡 Hint

Common Mistakes

Multiplying by the wrong cell that does not contain the scale factor.

Using a cell that contains a date instead of a number.

The duration in days is multiplied by the scale factor in D1 to get the length of the timeline bar.

3fill in blank

hard

Fix the error in the formula to correctly calculate the timeline start position relative to the project start date in cell A1.

Google Sheets

=DATEDIF([1], A2, "D")

Drag options to blanks, or click blank then click option'

AA1

BB1

CA2

DB2

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of dates in the formula.

Using the task start date as the first argument instead of the project start date.

The timeline start position is the number of days from the project start date in A1 to the task start date in A2. So, A1 must be the first date in the formula.

4fill in blank

hard

Fill both blanks to create a formula that shows the task name from column C with the duration in days if the duration is more than 5 days.

Google Sheets

=IF(DATEDIF(A2, B2, "D") [1] 5, [2], "")

Drag options to blanks, or click blank then click option'

A>

B<

CB2

DC2

EC2 & " (" & DATEDIF(A2, B2, "D") & " days)"

FB2 & " - " & C2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong comparison operator.

Not concatenating the task name and duration properly.

The formula checks if the duration is greater than 5 days. If yes, it shows the task name with the duration in days in parentheses.

5fill in blank

hard

Fill all three blanks to create a formula that calculates the timeline bar start position, length, and adds a label with task name and duration.

Google Sheets

=ARRAYFORMULA(IF(ROW(A2:A) = ROW(A2), "Label", IF(A2:A = "", "", {DATEDIF([1], A2:A, "D"), DATEDIF(A2:A, B2:B, "D") * [2], [3] & " (" & DATEDIF(A2:A, B2:B, "D") & " days)"})))

Drag options to blanks, or click blank then click option'

AA1

BD1

CB2:B

DC2:C

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong cell ranges for task names or scale factor.

Mixing up start and end dates in DATEDIF.

This formula uses ARRAYFORMULA to calculate for all rows: the start position as days from project start date A1, the bar length scaled by factor in D1, and a label combining task name in C2:C with duration.