Creating a Project Timeline in Excel: Dates, Durations, and Gantt Charts

Basic Timeline Structure

Essential columns:

• Task Name — What needs to be done
• Start Date — When work begins
• End Date — When work completes
• Duration — Calculated: =End-Start+1 (for inclusive count)
• Status — Not started / In progress / Complete
• Owner — Who's responsible

Calculating Durations

For calendar days (including weekends):

=EndDate - StartDate + 1

For business days only:

=NETWORKDAYS(StartDate, EndDate)

Add a holidays range to NETWORKDAYS to exclude company holidays.

End Date from Duration

Sometimes you know the start date and duration, not the end date:

=StartDate + Duration - 1

For business days:

=WORKDAY(StartDate, Duration - 1)

Simple Gantt Chart with Conditional Formatting

Create a date header row spanning your project timeline (one column per day or week).

1. Select the Gantt area (rows = tasks, columns = dates)
2. Conditional Formatting → New Rule → Formula
3. Formula: =AND(G$1>=$B2, G$1<=$C2)
4. Set fill color for the “bar”

This highlights cells where the column date falls between the task's start and end dates.

Milestone Markers

Milestones have zero duration — they mark a point in time, not a range.

Add a “Type” column to distinguish tasks from milestones. Format milestones differently in your Gantt (diamond symbol or different color).

Official Resources

• WORKDAY function — calculate end dates
• Create Gantt charts — Microsoft Gantt guide