Timeline charts in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate Duration for Timeline Chart

You have a start date in cell A2 and an end date in cell B2. Which formula correctly calculates the duration in days for a timeline chart?

A =B2 - A2

B =A2 - B2

C =DATEDIF(A2, B2, "D")

D =DATEDIF(A2, B2, "M")

💡 Hint

Remember, duration is end date minus start date.

The formula =B2 - A2 subtracts the start date from the end date, giving the duration in days. Option A reverses the order, giving a negative or wrong value. Option A uses DATEDIF with correct order to calculate days. Option A calculates months, not days.

❓ Function Choice

intermediate

Choose the Right Function to Extract Month

You want to group timeline events by month. Which function extracts the month number from a date in cell A2?

A =MONTH(A2)

B =YEAR(A2)

C =DAY(A2)

D =WEEKDAY(A2)

💡 Hint

Think about which part of the date represents the month.

The =MONTH(A2) function returns the month number (1-12) from the date. DAY returns the day, YEAR returns the year, and WEEKDAY returns the day of the week.

🎯 Scenario

advanced

Create a Timeline Chart with Overlapping Events

You have events with start and end dates that sometimes overlap. To visualize them clearly in a timeline chart, which approach helps avoid overlapping bars?

A Use only start dates and ignore end dates

B Sort events by start date only and plot directly

C Add a helper column assigning each event a row number to stagger bars vertically

D Plot events without any sorting or helper columns

💡 Hint

Think about how to separate overlapping events visually.

Assigning each event a row number in a helper column staggers the bars vertically, preventing overlap. Sorting alone doesn't prevent overlap. Ignoring end dates loses duration info. Plotting without sorting or helper columns causes bars to overlap and become unclear.

📊 Formula Result

advanced

Calculate Days Remaining from Today

Given an event end date in cell B2, which formula correctly calculates how many days remain from today until the event ends? Assume today is 2024-06-15.

A =DATEDIF(B2, TODAY(), "D")

B =B2 - TODAY()

C =DATEDIF(TODAY(), B2, "D")

D =TODAY() - B2

💡 Hint

Subtract today from the end date to get remaining days.

=B2 - TODAY() gives the number of days from today until the event ends. Option B reverses the order, giving negative days if event is in future. Option B uses DATEDIF correctly but is less common and can error if dates reversed. Option B reverses dates causing error.

❓ data_analysis

expert

Identify the Number of Timeline Events Longer Than 10 Days

You have a list of events with start dates in column A and end dates in column B (rows 2 to 11). Which formula correctly counts how many events last more than 10 days?

A =COUNTIFS(B2:B11, ">10", A2:A11, "<10")

B =COUNTIF(B2:B11 - A2:A11, ">10")

C =COUNTIF(B2:B11, ">10") - COUNTIF(A2:A11, ">10")

D =SUMPRODUCT((B2:B11 - A2:A11) > 10)

💡 Hint

Think about how to compare differences between two ranges.

Option D uses SUMPRODUCT to calculate the difference between end and start dates for each event and counts how many are greater than 10. Option D is invalid because COUNTIF cannot handle array subtraction. Option D subtracts counts of dates >10, which is meaningless here. Option D uses COUNTIFS with unrelated conditions causing wrong count.