Construction Schedule in Excel: Method and Formulas to Copy

A construction schedule is built in six steps: analyse the file, split the work into trades and tasks, estimate durations, sequence the tasks, assign the teams, then circulate the schedule. A spreadsheet the company already owns is enough, with no extra paid tool: one sheet with two zones, a task table on the left and a Gantt grid on the right.

The Gantt chart shows each task as a horizontal bar running from its start date to its end date. It shows what happens at the same time, what follows on, and what overlaps.

This guide does not offer a file to download. It describes the template (simple Gantt, trades, teams) to rebuild yourself in a few minutes: every column, every formula and every colour rule is given, with two dated examples to check your results. Save the finished file as a template for your next projects.

Which columns should the task table include?

The table has twelve forecast columns (A to L) and four actual-tracking columns (M to P). The Gantt grid starts in column Q, with no empty column. To add a column, for example the project number, use Excel's Insert command before column M: Excel adjusts the formulas already entered, but the letters cited in this guide shift, so check them again.

A milestone is a checkpoint with no duration of its own, such as the end of the structural work or the handover of the works.

To split a project, gather the input data, group the work into trades, then into tasks. This data comes from the signed quote, the drawings, access constraints, imposed dates and the list of contractors. Without this complete read-through, the schedule almost always overlooks a constraint.

• Quote: items, quantities and services sold, which define the scope. For a plumbing trade, the items to check in a plumber's quote help you verify what has been sold.
• Drawings and specification: nature of the works and logical order of execution.
• Access constraints: parking, floor level, material deliveries, imposed hours.
• Imposed dates: project start, delivery, occupation of the premises, desired handover.
• Contractors: your teams, your subcontractors and the other companies on site.

A trade package groups the work of a single trade, such as masonry, electrical work or painting. A task is a specific action within a trade package, with a start, an end and a person in charge. The ideal granularity is fine enough to be managed, broad enough to stay readable. If you cannot say in one sentence who does the task and when it is finished, it is too vague.

How do you adapt the breakdown to the trade?

• Second fix and finishing work: keep few trade packages (electrical, plumbing, plastering, painting) and detail the tasks, because the sequence between trades is tight.
• Renovation: provide trade packages for removal and preparation, and use the comment column for surprises found when walls are opened.
• Small general contractor: group by trade and distinguish what you carry out yourself from what you subcontract.

To estimate a duration, start from the quantity in the quote, the crew assigned to the task and your company's experience on comparable work. Then sequence the tasks with predecessors and identify the critical path, the chain of tasks that sets the project end date.

How do you estimate the duration of a task?

The reasoning comes down to three questions: what quantity must be done, how many people work on it, and how fast does your team usually progress. That speed comes from your past projects and your time records, not from a table of standard values. Without a history, ask the foreman or the subcontractor concerned and note the assumption in the comment column, so you can correct it after the project. To link these hours to your selling price, the method for setting your construction hourly rate explains the calculation.

Which estimation mistakes should you avoid?

• Forgetting setup, delivery and cleaning times, which do not appear in the quote.
• Assigning the same crew to two simultaneous tasks without checking that the team exists twice.
• Ignoring lead times for materials or joinery.
• Reusing a duration from a previous project without comparing areas, access and crew sizes.

How do you sequence tasks?

A dependency links two tasks. The most common is the finish-to-start dependency: the next task starts only once the previous one is finished, for example painting after plastering. Tasks with no direct link run in parallel, such as the electrical work in one room and the plumbing in another, if teams and access allow.

How do you handle unavoidable delays?

Some delays cannot be reduced by adding people: the drying of a plaster coat or a screed is one example. Enter them as tasks in their own right, with no team assigned, using the duration recommended by the manufacturer. Count it in calendar days, that is, days of the calendar, worked or not, since drying continues over the weekend.

What is the critical path?

The critical path is the chain of linked tasks whose delay directly pushes back the project end date. Tasks outside this path have some float, the others have none. Identify it by following the predecessors from the end of the project back to the start: that is where you concentrate your vigilance.

Enter only the start date, the first day of the project, in E5. It must be a working day, that is, a worked day: not a Saturday, not a Sunday, not a public holiday from your list. Otherwise the Gantt grid shifts or hides the first bar. To check it, enter in a free cell =WORKDAY(E5-1,1,Holidays)=E5: the result TRUE confirms a working day.

The WORKDAY function adds a number of working days to a date, excluding weekends and a list of public holidays. Enter this list in a named range called Holidays (Formulas menu, Define Name). The predecessor is the task that must finish before another starts. In this template, task n is in row n+4.

Which formulas for end and start dates?

End date: in F5, enter =IF(D5=0,E5,WORKDAY(E5,D5-1,Holidays)) then copy down. The "-1" counts the start day as the first worked day: a 3-day task starting on a Monday ends on Wednesday. For a milestone, the duration is 0 and the end equals the start.

Start date: row 5 stays entered by hand. From row 6, with the first predecessor in G6 and the second, optional, in H6, enter in E6 =IF(G6="",$E$5,WORKDAY(MAX(INDEX($F$5:$F$40,MATCH(G6,$A$5:$A$40,0)),IF(H6="",0,INDEX($F$5:$F$40,MATCH(H6,$A$5:$A$40,0)))),1,Holidays)) then copy down. MATCH finds the row of each predecessor, INDEX reads its end date, MAX keeps the later one and the task starts on the next working day. If your company works some Saturdays, use WORKDAY.INTL, which lets you choose the days off.

Which rules govern the start formula?

A task whose cell G is empty starts on the start date in E5; fill in H only if G is filled. A number in G or H that is absent from column A displays #N/A: this error is intended, it flags a deleted or mistyped predecessor, and it is better not to hide it with IFERROR, because a wrong schedule with no alert is worse than a schedule in error. If you move the first task, re-enter the start date in the new row 5.

The drying of a plaster coat or a screed is counted in calendar days, that is, weekends and public holidays included, because it continues even when the site is stopped. The WORKDAY function would wrongly calculate it in working days: a dedicated formula is needed on this row.

In the template, task n is in row n+4 and drying is a task with no team. On the drying row, enter the duration in calendar days in column D, then in E the end of the predecessor plus one day, for example =INDEX($F$5:$F$40,MATCH(G10,$A$5:$A$40,0))+1 in row 10, and in F the formula =E10+D10-1. A plaster coat that ends on a Friday gives a 2-day drying period, from Saturday to Sunday.

When does the task following a drying period start?

The following task keeps the start formula of the other tasks. It takes the end date of the drying and starts on the first working day strictly after it, even if that end falls on a Saturday, a Sunday or a public holiday from your list: after a drying period that ends on Sunday, the next task starts on Monday.

Which display limit should you know about?

The Gantt grid shows only working days, so the bar of a drying period that falls on a weekend appears shortened or missing. Rely then on the dates in columns E and F, which remain accurate.

The Gantt grid occupies the columns from Q, right after column P of the actual tracking. Row 4 contains one date per column and the first task is in row 5. This date row is filled with two formulas.

In Q4, enter =E5 to pick up the start date. In R4, enter =WORKDAY(Q4,1,Holidays) and copy to the right as far as column BD. The grid then covers 40 working days, one per column, weekends and public holidays excluded.

For a longer project, copy the last column further to the right and extend the range of the colour rules. To check that the schedule fits in the grid, enter in a free cell =MAX($F$5:$F$40)<=BD4: the result TRUE confirms that all task end dates are displayed.

A conditional format is a rule that colours a cell according to a condition. Select the range Q5:BD40 of the Gantt grid, then create three rules of the type "use a formula to determine which cells to format", in this order, with the "Stop if true" option ticked:

1. Milestone, with a distinct colour: =AND($J5="Yes",Q$4=$E5)
2. Progress, with a dark colour: =AND(INT(N($D5)*N($K5))>=1,Q$4>=$E5,Q$4<=$F5,Q$4<=WORKDAY($E5,INT(N($D5)*N($K5))-1,Holidays))
3. Planned task, with a light colour: =AND(Q$4>=$E5,Q$4<=$F5)

How does the progress rule colour the days?

The progress rule colours only whole days completed: INT keeps the integer part of the product duration × progress. A 3-day task at 50% colours 1 day (1.5 rounded down to 1), at 70% it colours 2 (2.1 rounded down to 2), and at 1% it colours none, which avoids overstating progress. Leave column K empty on drying rows: their duration is in calendar days, whereas the rule counts in working days, and the light bar is enough.

Which precautions apply to milestones and rows?

The milestone appears only if its date is in row 4: it must therefore be a working day and stay within the grid. The start formula always produces a working day, provided you do not overwrite it with a manual entry; the check =MAX($F$5:$F$40)<=BD4 covers the risk of falling outside the grid. The dollar sign before the digit 4 fixes the date row; the dollar sign before the letters fixes the start, end, milestone, duration and progress columns. Column K must contain a percentage.

To add a trade, insert rows in the middle of the table, not at the end, so that formulas and rules extend. To remove one, delete the entire rows and check that no predecessor points to them.

To avoid a team being scheduled in two places at once, assign each task to a named team or person, then check for date overlaps by team. Do this check at every change to the schedule, not only at the start.

Should you assign by team or by person?

Assigning by team works when the same group always works together: column I contains "Team 1" or "Team 2". Assigning by person becomes useful when teams are reshuffled or when an employee has a rare skill. Always use the same labels: a spelling difference distorts sorting and checks. For a subcontractor, name them in column I and check that the subcontracting contract covers the planned dates. Then sort the table by team and then by start date: gaps and overlaps appear immediately.

Which formula detects a double booking?

Create a conditional formatting rule that colours the rows whose team is already busy in the same period. Select the table rows (for example A5:L40) and enter:

=AND($I5<>"",$I5<>"None",COUNTIFS($I$5:$I$40,$I5,$E$5:$E$40,"<="&$F5,$F$5:$F$40,">="&$E5)>1)

The COUNTIFS function counts the rows that meet several conditions at once: same team, start less than or equal to the end of the tested row, end greater than or equal to its start. Because the tested row counts itself, a result of 1 is normal; the rule colours only results greater than 1. The test on empty cells and on "None" avoids flagging tasks without a team, such as drying. To test the rule, assign the plumbing of example 1 in this guide to "Team 2": it overlaps the cable routing, and both rows should be coloured.

With several projects in parallel, group the tasks in a consolidation tab, with a project column, then apply the sort and the rule there.

Fictional, illustrative example This schedule is invented to show the method. The durations and the start date (Monday, January 4, 2027) are fictional, and the Holidays list is assumed empty over the period. Replace them with your own data.

Here is a completed house renovation schedule, with its dates, to check an Excel file built following this guide. Durations are in working days, except drying, counted in calendar days. The date of Monday, January 4, 2027 is entered in E5; the other dates are calculated by the template formulas, and task n is in row n+4.

• Parallelism and two predecessors: the electrical and plumbing work start after the removal with two different teams; the partitions (row 8) wait for both, with 2 in G8 and 3 in H8.
• Unavoidable delay: the drying (row 10) has no team and ends on a Sunday; painting starts on Monday, the first working day that follows.
• Milestone and teams: the handover has a duration of 0 and "Yes" in column J; team 1 works on tasks 1, 4, 5 and 8 with no overlap.

This second fictional example shows the structural works schedule of a new house, dated from Monday, January 4, 2027, with the Holidays list assumed empty. Almost all the trades follow on in finish-to-start. Concrete requires a hardening time before loading, to be entered in calendar days according to the structural engineer's or manufacturer's requirements; the value shown below is fictional.

• Hardening: row 8 counts 7 calendar days, Saturday and Sunday included; the masonry starts on Monday, January 25, the first working day that follows.
• Subcontractor: the earthworks and the roof frame are entrusted to a subcontractor, whose dates must match those of their contract.
• Milestone: the weathertight stage has a duration of 0 and "Yes" in column J.

Use these dates to check your formulas. If your file gives different ones, first re-read the predecessors in G and H, the durations in D and the Holidays list.

To track a schedule during the project, set a regular update frequency, fill in the progress of each task and note the actual dates next to the planned dates. Start and end variances are then calculated by difference. A schedule that is not updated no longer reflects the project and no longer helps decisions.

Which update routine should you adopt?

• Frequency: choose a rhythm suited to the length of the project, for example at the end of each week, and stick to it.
• Progress: fill in column K as a percentage. This completed share also feeds the progress payment statement used to invoice progress.
• Actual dates: enter the actual start in column M and the actual end in column N, without overwriting the planned dates in columns E and F.
• Comment: note in column L the cause of each variance (access impossible, late delivery, bad weather, additional work).

How do you calculate the variance between planned and actual?

Two columns measure the variance: O for the start, P for the end. In O5, formatted as a number and not as a date, enter =IF(M5="","",IF(M5>E5,NETWORKDAYS(E5+1,M5,Holidays),IF(M5<E5,-NETWORKDAYS(M5+1,E5,Holidays),0))). In P5, enter the same formula replacing M5 with N5 and E5 with F5. The formula counts the working days between the planned date and the actual date, excluding the planned date: it stays correct even if one of the two dates falls on a weekend. For a variance in calendar days, use =IF(N5="","",N5-F5).

A positive result indicates a delay, a negative result an advance. Fictional example on the plaster coats of example 1: planned start Thursday, January 14, actual start Friday, January 15, start variance of 1 working day; planned end Friday, January 15, actual end Tuesday, January 19, end variance of 2 working days (Monday and Tuesday) but 4 calendar days. If the delayed task belongs to the critical path, the project end date is at risk.

To circulate a construction schedule, export it as a PDF from the approved version, after hiding the internal tracking columns, and indicate the date and the version number. Each subcontractor receives their own rows, not the whole file.

Before the works start, save a copy of the approved schedule, named for example "baseline" with the date: it no longer changes and serves as a point of comparison. To circulate an up-to-date version, define a print area (Page Layout menu), switch to landscape orientation, fit all columns on one page, then export to PDF. Before exporting, hide the actual tracking columns and the internal comments. Send each subcontractor their rows, by filtering on column I, and archive the old versions.

Delays, penalties and bad weather Execution deadlines and any late-completion penalties depend on the contract or the order signed with the client: re-read them before circulating the schedule, and have a legal adviser review them in case of dispute. Under the regulations in force in France, days lost to bad weather may fall under the French bad-weather unemployment scheme (chômage intempéries): check the applicable conditions with the CIBTP. Note them in the schedule's comment column. The articles on project delays and bad weather cover these topics in detail.

Excel is enough as long as the company manages few projects at a time, with few teams, and a single person maintains the schedule. Beyond that, the limits of the spreadsheet appear: manual entry, multiple versions, difficult sharing with the field. They stem from the use asked of it, not from a defect in the spreadsheet.

• Manual updates: every date change requires checking the predecessors and the formulas.
• Multiple versions: the file sent by email exists in several copies.
• Sharing with the field: viewing or editing a spreadsheet on a phone remains impractical.
• Calendar-day delays: the grid shows only working days, which hides drying periods that fall on a weekend.
• Copying risk: a badly copied formula gives no warning, hence the value of checking the file with the dated examples in the guide.

Which criteria for choosing between the spreadsheet and a dedicated tool?

If several of these criteria apply to you, the question becomes the time you spend on the spreadsheet. Constrigo presents its field planning module and a page devoted to tracking field projects: consult them to learn precisely what they do.

Key takeaways A construction schedule is built without a paid tool, in a spreadsheet, in six steps: analyse, split, estimate, sequence, assign, circulate. The start date entered in E5 must be a working day; the other dates and the grid bars are calculated by formulas. Drying is counted in calendar days, the other tasks in working days. Check your file with the two dated examples in the guide and the test =MAX($F$5:$F$40)<=BD4 before circulating it. The schedule has value only if it is updated, with start and end variances, compared with a baseline version and circulated with its version date. Deadlines and late-completion penalties depend on the contract or the order signed with the client: re-read them before circulating the schedule.