Chart & Visualization Issues

How to Create a 24-Hour Visual Timeline or Gantt Chart in Excel

Khadija Khan Oct 20, 2026 869 views

Question details

The user wants to create a 24-hour visual timeline to display tasks with specific start and end times, showing their durations as color-coded blocks.

How to Create a 24-Hour Visual Timeline or Gantt Chart in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking daily schedules, task assignments, or project phases across a continuous 24-hour timeline using precise minutes or rounded 30-minute intervals.
Observed behavior: Requires a visual chart mapping exact time durations (like 12:27 to 3:52) onto a proportional 24-hour horizontal axis without manually drawing shapes.

Before you start

Ensure your task data is organized into three distinct columns: Task Name, Start Time, and End Time, properly formatted as Time values in your spreadsheet.

Solution 1Recommended

Build a Timeline Using a Stacked Bar Chart

The most effective method to create an accurate visual timeline in Excel is by stacking task durations next to a hidden start-time series.

Excel does not have a built-in Gantt chart type. However, by calculating the exact duration of each task and formatting a Stacked Bar Chart so the initial data series becomes transparent, you can create a highly accurate timeline that mimics a professional Gantt chart.

Calculate Task Durations

Create a new column titled 'Duration'. Subtract the Start Time from the End Time for each task (e.g., enter `=C2-B2`) and format the result as Time.

Insert a Stacked Bar Chart

Highlight your Task Names, Start Times, and Durations. Navigate to the 'Insert' tab, click on the 'Column or Bar Chart' icon, and select 'Stacked Bar'.

Hide the Start Time Series

Click directly on the Start Time data series (the first segment of the bars) in the chart. Right-click, choose 'Format Data Series', and change both the Fill and Border settings to 'No Fill' and 'No Line'.

Format the 24-Hour Axis

Right-click the horizontal (Time) axis and select 'Format Axis'. Set the Minimum bound to 0.0 and the Maximum bound to 1.0 (representing a full 24-hour day). Adjust the Major Unit to dictate your interval ticks.

Build a Timeline Using a Stacked Bar Chart

Rounding Task Times: If you want task blocks to snap to neat half-hour increments rather than exact minutes, use the MROUND function on your raw time data (e.g., `=MROUND(B2, "0:30")`) before creating the chart.

Visualize Data with WPS

How to Create a 24-Hour Timeline in WPS Spreadsheet

WPS Spreadsheet offers powerful charting tools that are fully compatible with Microsoft Excel, allowing you to easily build detailed 24-hour Gantt charts and visual schedules.

1. Prepare Data Columns: Enter your Task Names and Start Times, then calculate the Duration column by subtracting the start time from the end time.
2. Insert a Stacked Bar: Select the three columns, go to the Insert tab, select Chart, and choose the 2D Stacked Bar option.
3. Make Start Times Transparent: Double-click the first data series (Start Time) in the chart and set its Fill setting to 'No Fill' to reveal the floating duration blocks.
4. Adjust the Timeline Axis: Right-click the horizontal time axis to open properties, formatting the minimum and maximum bounds for your 24-hour scale.

Fully compatible with Microsoft Excel charts and time formattingEasily create professional Gantt charts using robust stacked bar featuresFree, lightweight, and features an intuitive interface for seamless transitionsAdvanced axis customization for perfect time-interval mapping

microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate precise 30-minute units for the axis scale?

Spreadsheets store time as fractions of a day, where 1.0 equals 24 hours. To set 30-minute intervals on your horizontal axis, change the Major Unit to 0.020833 (which is 30 minutes divided by 1440 minutes in a day).

Why are my times showing as decimal numbers instead of hours on the chart?

This happens when the axis format reverts to standard numerical display. Right-click the horizontal axis, select 'Format Axis', scroll down to the 'Number' section, and change the category from 'General' to 'Time'.

Can I color-code different task blocks on the same timeline?

Yes. Once the chart is built, you can click twice slowly on an individual task block (data point) to select only that block, then change its shape fill color to manually color-code tasks by category or status.

Excel & Data Problems