24-Hour Gantt Chart Excel: 24 Setup Ideas That Work

To make a 24-hour Gantt chart in Excel, put 24 hourly time values across the columns, list tasks with start and end times in rows, then use conditional formatting to shade each task’s time span. The 24 options below cover practical layouts, time scales, shift and event schedules, and ways to keep the chart readable; they are for Microsoft Excel for Microsoft 365 on Windows, with notes for adapting the setup.

Layouts for a Full-Day Timeline

Start by deciding what one row represents and whether the chart needs to show a whole day at once. A spreadsheet grid is often easier to customize than a chart object when the schedule needs task names, owners, and exact start and finish times beside the timeline.

Midnight-to-midnight hourly grid. Use 24 columns from 00:00 through 23:00 when a quick view of one calendar day matters more than minute-level precision.
One row per task. Put task names in rows and start and finish times in separate columns so people can scan the schedule and change an assignment without redrawing bars.
One row per person. Use a separate row for each staff member when the main question is who is working at each hour.
One row per resource. Assign rows to rooms, machines, or vehicles when the schedule is about shared capacity rather than individual work items.
Event run-of-show layout. Put speakers, cues, or activities in rows and use short time blocks to coordinate a conference, broadcast, or live production.

A midnight-to-midnight view is best for a single date. If the schedule includes work that crosses midnight, keep the date as well as the time in the underlying values; a time-only entry such as 01:00 cannot tell Excel whether it means early today or early tomorrow. You can still display only the clock time in the grid while retaining full date-and-time values for calculations.

Time Scales and Cell Setup

The timeline interval controls how precisely the bars show task boundaries. More columns improve detail but make the sheet wider, so match the resolution to the schedule’s real decision-making needs instead of adding tiny intervals by default.

Thirty-minute columns. Choose 48 time slots when shift handovers or appointments routinely begin on the half hour.
Fifteen-minute columns. Use 96 slots for production cues, clinic bookings, or other schedules where quarter-hour changes matter.
Five-minute columns. Reserve 288 slots for tightly timed operations, since this level of detail is cumbersome for a general staffing plan.
Visible 24-hour clock labels. Format timeline headings as hh:mm so afternoon times appear as 13:00 rather than 1:00 PM.
Elapsed-time display for the endpoint. Use the custom format [h]:mm for a duration or endpoint that must display as 24:00 instead of wrapping to 00:00.

In Excel for Microsoft 365 on Windows, select the relevant cells and press Ctrl+1 to open Format Cells; choose Number, then Custom, and enter hh:mm for clock labels or [h]:mm for elapsed hours. On Mac, Command+1 opens Format Cells. A time is stored as part of a day, so 12:00 is one-half and 24:00 is the next day boundary, represented by 1 when entered as a time value. Apply the display format to the cells rather than typing labels as text, because numeric times can be compared and used by conditional formatting.

For a simple hourly header, enter 00:00 in the first timeline cell and add one hour in each cell to its right. If you need a finer scale, add 30, 15, or 5 minutes instead. Check the final heading: a 24-column day should end at 23:00, while the boundary after that day is 24:00 on the next date.

Ways to Draw and Read the Bars

Conditional formatting turns the timetable cells into Gantt bars without inserting shapes. It is easy to update when a task moves, and the underlying cells remain available for formulas, filters, and printing.

Conditional-formatting task bars. Shade timeline cells based on each row’s start and finish values when the schedule needs to update automatically after edits.
Separate colors by work type. Use a small, consistent palette for categories such as setup, service, and cleanup so readers can identify phases without relying on color alone.
Separate colors by status. Distinguish planned, active, and complete tasks when progress matters as much as timing.
Highlight the current hour. Add a distinct fill to the current time column for a live operations board, and update it as the day advances.
Mark breaks and unavailable periods. Shade nonworking time in a background color so a blank cell cannot be mistaken for available capacity.

For a basic conditional-formatting rule, assume the timeline headings are in F4:AC4, task starts are in C5:C20, task finishes are in D5:D20, and the bar grid is F5:AC20. Select F5:AC20, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(F$4<$D5,F$4+TIME(1,0,0)>$C5) and choose a fill. This checks whether each one-hour cell overlaps the task interval, rather than requiring the task to start exactly on the hour. For a task from 08:30 to 10:15, the 08:00, 09:00, and 10:00 cells are shaded, while 07:00 and 11:00 are not. The formula assumes one-hour columns; for half-hour columns, replace TIME(1,0,0) with TIME(0,30,0).

Those references are set up so the column heading changes as the rule moves across the grid, while each task’s start and finish remain in columns C and D. If the chart runs into the following date, use actual date-and-time values in the headings and task cells so the comparisons remain in sequence. For labels, use text or symbols in addition to color when the workbook may be printed in grayscale or used by people who distinguish colors differently.

Schedules for Shifts and Multi-Day Work

Some 24-hour charts describe a repeating cycle; others show one specific day or a handover into tomorrow. Choose a structure that makes the date boundary explicit, especially when overnight shifts are involved.

Overnight-shift schedule. Store start and finish as date-and-time values when a shift begins before midnight and ends the next morning.
Day-and-night swimlane. Divide each day into day and night bands when two teams share responsibility across a continuous operation.
Two-day handover view. Extend the timeline into the next date when the handover itself needs to be visible beside the original day’s tasks.
Recurring daily template. Keep a clean, time-only pattern for repeatable routines, then copy it to dated sheets when each date needs its own record.
Weekend-aware schedule. Add the actual date above each 24-hour block when a sequence includes weekdays and weekends with different staffing rules.

For an overnight job, a start of 22:00 and an end of 02:00 look reversed if they are stored as clock times alone. Enter the start and finish with dates, such as a start on one date at 22:00 and a finish on the next date at 02:00. The timeline headings must also carry the dates, even if the cells display only hh:mm. That lets the same overlap rule work across midnight and avoids treating the finish as earlier than the start.

Useful Variations for Sharing and Tracking

A chart is more useful when it answers a specific operational question. Add only the supporting details that help someone make a decision, and keep the time grid as the visual center of the sheet.

Owner-and-task board. Add an owner column beside each task so a manager can see responsibility and timing together.
Dependency-aware schedule. Add a predecessor column for tasks that must happen in sequence, since a Gantt bar alone does not explain why work cannot start earlier.
Planned-versus-actual view. Use adjacent rows or a second set of start and finish columns when comparing the intended schedule with what happened.
Print-friendly daily sheet. Set a print area around one day’s tasks and timeline when the schedule must be posted or used away from a screen.

For printing, use Page Layout > Print Area > Set Print Area after selecting the schedule, then check Print Preview before distributing it. A 15-minute timeline can become very wide on paper, so consider landscape orientation and a readable scaling choice rather than shrinking the sheet until the labels are illegible. For a shared workbook, freeze the task-name columns and time-heading row so people can scroll through a long schedule without losing context.

How to Use This List

Choose the layout based on the question your chart must answer. If you need to know when tasks happen, start with one row per task. If you need to know who or what is occupied at a given time, assign rows to people or resources instead. For a single event, a run-of-show layout can combine the activity, responsible person, location, and timing without creating a separate chart for every team.

Next, pick the smallest useful interval. Hourly columns are compact and work for broad staffing plans; half-hour or quarter-hour columns suit appointments and handovers. Five-minute columns are worthwhile only when their extra precision changes what someone does. Avoid mixing intervals in one grid unless the layout clearly explains the change, since uneven columns can imply that all time blocks have equal duration.

Build a small example before filling the whole day. Enter a few tasks with known start and finish times, including one that starts between grid boundaries and one that ends at the next day. Check that the bar includes the correct cells and that the midnight transition is clear. Then add owners, status, break periods, and colors only when they help readers interpret the schedule. A compact legend and a printed date prevent a reusable template from being mistaken for a live plan.

Finally, keep the inputs separate from the visual grid. Store task names, owners, dates, start times, and end times in dedicated columns, and use conditional formatting only to display their overlap with the timeline. This makes edits easier to audit and reduces the risk that someone changes a colored cell instead of correcting the task time. Protecting formula or formatting cells can help in a shared workbook, but leave the intended input cells editable.

Frequently Asked Questions

Can Excel make a 24-hour Gantt chart without a chart object?
Yes. A time grid with conditional formatting can display Gantt-style bars directly in worksheet cells. This approach is often simpler for an operational schedule because task details and the timeline stay together.

How do I show 24:00 instead of 00:00?
Use the custom number format [h]:mm for an elapsed endpoint that should display 24:00. For a clock-time label on the next day, use a date-aware value and display the date separately so midnight is not confused with the previous day’s start.

Will the chart handle tasks that cross midnight?
Yes, if task starts, finishes, and timeline headings include dates as well as times. Time-only values cannot reliably distinguish 02:00 today from 02:00 tomorrow.

Can I use 15-minute or 30-minute intervals?
Yes. Create headings at the chosen interval and adjust the conditional-formatting overlap formula to use the same interval length. A finer scale increases the number of columns, so check the sheet’s readability and print layout.

Is this the same as an Excel Gantt chart template?
Not necessarily. This is a buildable worksheet layout rather than a promise about a specific downloadable template; Microsoft’s available templates can vary, and many project templates are designed for dates rather than a single 24-hour day.

Final Thoughts

A useful 24-hour Gantt chart in Excel starts with real time values, a clear interval, and a layout that matches the work being coordinated. Use date-and-time values for overnight schedules, conditional formatting for responsive bars, and only as much detail as readers can act on. Begin with an hourly grid, test the midnight boundary, and refine the design when a real scheduling need calls for it.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.