Timeline charts in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a project coordinator at a software development company.

📋 Request: Your manager wants a clear visual timeline chart showing the start and end dates of multiple projects to track progress and deadlines.

📊 Data: You have a list of projects with their names, start dates, and end dates.

🎯 Deliverable: Create a timeline chart in Google Sheets that visually displays each project's duration along a date axis.

Sample Data

Project Name	Start Date	End Date
Website Redesign	2024-06-01	2024-06-20
Mobile App Launch	2024-06-10	2024-07-05
Marketing Campaign	2024-06-15	2024-07-10
Customer Survey	2024-06-05	2024-06-25
Security Audit	2024-06-20	2024-07-01
New Feature Dev	2024-06-18	2024-07-15

1

Step 1: Add two new columns to calculate the project duration and the days from the earliest start date.

In cell D2, enter: =C2-B2+1 (to calculate duration in days) In cell E2, enter: =B2-MIN($B$2:$B$7) (to calculate offset days from earliest start date)

Expected Result

Column D shows project durations (e.g., Website Redesign: 20 days), Column E shows days offset from earliest start date (e.g., Website Redesign: 0 days)

2

Step 2: Fill down the formulas in columns D and E for all projects.

Drag the fill handle from D2 and E2 down to row 7.

Expected Result

All projects have duration and offset days calculated correctly.

3

Step 3: Select the data range for the timeline chart: Project Name, Offset Days, and Duration.

Select cells A2:A7, E2:E7, and D2:D7 together.

Expected Result

Selected data includes project names, start offsets, and durations.

4

Step 4: Insert a stacked bar chart to represent the timeline.

Go to Insert > Chart. In Chart Editor, choose 'Stacked bar chart'. Set 'Project Name' as the vertical axis labels, 'Offset Days' as the first series (make it transparent), and 'Duration' as the second series (colored).

Expected Result

Chart shows bars starting at the correct offset with length equal to project duration, visually representing timelines.

5

Step 5: Format the chart for clarity: make the 'Offset Days' series transparent and add axis titles.

In Chart Editor > Customize > Series, select 'Offset Days' series and set fill color to none or transparent. Add horizontal axis title 'Days from start' and vertical axis title 'Projects'.

Expected Result

Timeline chart clearly shows project durations aligned by start dates with no visible offset bars.

Final Result

Project Timeline Chart

| Website Redesign  | ████████████████████          |
| Mobile App Launch |       ████████████████████    |
| Marketing Campaign|          █████████████████████|
| Customer Survey   |   ███████████████             |
| Security Audit    |           ████████████       |
| New Feature Dev   |            ██████████████████|

(Days from earliest start date shown horizontally)

✓Website Redesign starts earliest and lasts 20 days.

✓Mobile App Launch and Marketing Campaign overlap in mid-June to early July.

✓Customer Survey starts early but finishes before Mobile App Launch.

✓Security Audit and New Feature Development start later but overlap with ongoing projects.

Bonus Challenge

Add conditional formatting to the timeline chart to highlight projects that last longer than 20 days.

Show Hint

Use a helper column to mark projects with duration > 20 days, then adjust the chart series color based on this condition.