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.

Practice

1. What is the main purpose of a Timeline chart in Google Sheets?

easy

A. To sort data alphabetically

B. To calculate sums of numbers in a column

C. To show events over time with start and end dates

D. To create pie charts from data

Solution

1. Step 1: Understand what a Timeline chart displays

   A Timeline chart visualizes events along a time axis, showing when they start and end.

2. Step 2: Compare options to the Timeline chart purpose

   Only To show events over time with start and end dates describes showing events over time with start and end dates, which matches the Timeline chart's use.

3. Final Answer:

   To show events over time with start and end dates -> Option C

4. Quick Check:

   Timeline chart = show events over time [OK]

Hint: Timeline charts always show events with start and end dates [OK]

Common Mistakes:

• Confusing Timeline charts with pie charts
• Thinking Timeline charts calculate sums
• Assuming Timeline charts sort data

2. Which of these is the correct way to insert a Timeline chart in Google Sheets?

easy

A. Select your data, then press Ctrl + C

B. Select your data, then go to Insert > Timeline chart

C. Right-click a cell and choose Insert > Pie chart

D. Use the formula =TIMELINE() in a cell

Solution

1. Step 1: Recall how to insert charts in Google Sheets

   Charts are inserted via the Insert menu after selecting data.

2. Step 2: Identify the correct menu option for Timeline charts

   Select your data, then go to Insert > Timeline chart correctly states Insert > Timeline chart after selecting data.

3. Final Answer:

   Select your data, then go to Insert > Timeline chart -> Option B

4. Quick Check:

   Insert menu > Timeline chart = correct method [OK]

Hint: Always use Insert menu to add charts, not formulas [OK]

Common Mistakes:

• Trying to insert Timeline chart with a formula
• Copy-pasting data instead of inserting chart
• Choosing wrong chart type like Pie chart

3. Given this data in Google Sheets:

Event | Start Date | End Date
A     | 2024-01-01 | 2024-01-05
B     | 2024-01-03 | 2024-01-10
C     | 2024-01-08 | 2024-01-12

If you create a Timeline chart from this data, what will it show?

medium

A. A pie chart dividing events by duration

B. Only Event A because it starts first

C. A bar chart showing event counts

D. Events A, B, and C displayed on a timeline with overlapping dates

Solution

1. Step 1: Understand Timeline chart behavior with overlapping events

   Timeline charts show all events with their start and end dates, even if they overlap.

2. Step 2: Analyze the given data

   Events A, B, and C have overlapping date ranges, so all appear on the timeline overlapping accordingly.

3. Final Answer:

   Events A, B, and C displayed on a timeline with overlapping dates -> Option D

4. Quick Check:

   Timeline chart shows all events with their date ranges [OK]

Hint: Timeline charts show all events even if dates overlap [OK]

Common Mistakes:

• Thinking only the first event shows
• Confusing Timeline chart with bar or pie charts
• Assuming overlapping events are merged

4. You tried to create a Timeline chart but it shows an error. Which of these fixes the problem?

Event | Start Date | End Date
Task1 | 2024-02-10 | 2024-02-05

What should you do?

medium

A. Swap the Start Date and End Date so Start Date is before End Date

B. Delete the End Date column

C. Change the Event name to a number

D. Use a pie chart instead

Solution

1. Step 1: Identify the error cause in Timeline chart data

   The Start Date must be before or equal to the End Date; here it is after, causing error.

2. Step 2: Fix the date order

   Swapping the Start Date and End Date so the start is earlier fixes the timeline error.

3. Final Answer:

   Swap the Start Date and End Date so Start Date is before End Date -> Option A

4. Quick Check:

   Start Date ≤ End Date fixes Timeline chart errors [OK]

Hint: Ensure Start Date is before End Date to avoid errors [OK]

Common Mistakes:

• Deleting End Date column breaks timeline
• Changing event names to numbers is unnecessary
• Switching to pie chart ignores timeline purpose

5. You have a project plan with tasks and their start and end dates. You want to create a Timeline chart that shows tasks grouped by team member. How can you prepare your data in Google Sheets to do this?

hard

A. Add a column for team member and include it in the data range before inserting the Timeline chart

B. Create separate sheets for each team member and make a Timeline chart for each

C. Use a pie chart to show team member task distribution

D. Sort tasks by start date and insert a bar chart

Solution

1. Step 1: Understand Timeline chart grouping feature

   Timeline charts can group events by a category column like team member if included in the data.

2. Step 2: Prepare data with team member column

   Add a column listing team members for each task and select all columns when inserting the Timeline chart.

3. Final Answer:

   Add a column for team member and include it in the data range before inserting the Timeline chart -> Option A

4. Quick Check:

   Grouping in Timeline charts needs category column included [OK]

Hint: Include grouping column in data range to group Timeline events [OK]

Common Mistakes:

• Making separate charts instead of grouping
• Using pie or bar charts which don't show timelines
• Not including the grouping column in the chart data