Create a Timeline in Excel

What this does

A schedule in Excel is one table plus a small amount of date arithmetic, and the arithmetic is where it usually goes wrong. Dates are numbers, so End − Start gives days directly; the trap is that it counts weekends. WORKDAY(start, days, [holidays]) skips Saturdays, Sundays and any dates in a holiday range you supply, and NETWORKDAYS(start, end, [holidays]) counts working days between two dates the same way. For shift rosters that cross midnight, End − Start goes negative, and =MOD(End-Start, 1) fixes it. The visual half is optional: a stacked bar chart where the first series is the start offset formatted as No Fill and the second is the duration produces a Gantt-style timeline without any add-in. Conditional formatting on a grid of dates is the lighter alternative when a rectangle per day is enough. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “create a timeline in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

Tasks in A2:A20, Start in B, Duration in days in C. D2: =WORKDAY(B2, C2) gives the finish date skipping weekends. Add a Holidays named range and it skips those too: =WORKDAY(B2, C2, Holidays). For the chart: select A2:C20 ▸ Insert ▸ Bar ▸ Stacked Bar, click the first series ▸ Format ▸ No Fill, then reverse the category axis so task 1 sits at the top. Every schedule question reduces to the same two things — dates that skip non-working days, and one column that is calculated rather than typed twice. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Nothing on this page is behind a login: the tool runs entirely in your browser, the formula is shown in full with one-click copy, and the steps work the same on Windows and Mac. That is the whole promise here — the exact answer, a way to prove it on your own numbers, and just enough context to make it stick. Treat “create a timeline in excel” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

Common mistakes

• Subtracting dates and calling the result working days — plain subtraction counts weekends.
• Typing both duration and end date, so the two disagree the first time one is edited.
• Ignoring holidays, which makes every downstream date optimistic by a few days.
• Formatting a shift total as h:mm instead of [h]:mm, which wraps anything over 24 hours back to zero.
• Building the Gantt chart before the dates are right, then adjusting bars by hand instead of fixing the formula.

Frequently asked questions