Construction Change Order Tracking in Excel: The Deadline Column Your Log Is Missing

A general contractor on a $3.4 million municipal addition opens his change order log on the morning of September 17. Eight items are open, $197,500 of face value. Every row has a number, a description, a dollar amount, and a status cell that says "Submitted" or "Pending." The log is current. It is also worthless for the only question that matters this week, which is: whose notice window has already closed? Two of these were noticed after their window shut. A third window shuts tomorrow. His construction change order tracking Excel file cannot tell him, because it does not contain a single date that the contract cares about.

This is the most expensive habit in small and mid-size construction. The log tracks money and status. The contract pays on dates. Those are different data models, and when they disagree, the contract wins. On this one project, the gap between the two is $55,585.

The Log Everybody Builds, and the Question It Cannot Answer

Here is the log as it usually exists. Four columns, maintained faithfully, reviewed at the owner meeting, attached to the pay application.

Read that table and try to answer three questions. Which of these is legally dead? Which one dies this week? How much of the $197,500 will actually land? You cannot, and neither can the project manager who built it. "Submitted" tells you a PDF left the office. It does not tell you whether it left in time.

The Same Clause Number, Five Contracts, Three Different Deadlines

Most contractors assume there is one rule. There is not. The standard AIA A201-2017 general conditions set a 21 day window for initiating a claim, running from the event or from when the contractor first recognized the condition, whichever is later. The AIA's own documentation describes claims arising before the end of the correction period as subject to a 21 day notice period. But almost nobody signs the document as published. Owners edit it, and the edits land precisely on the notice clauses.

Here are five copies of "AIA A201-2017 General Conditions," all published openly by the owners who use them, all carrying the same section number. Between them they set three different notice periods and three different consequences for blowing one.

Seven days versus thirty is not a detail. That is more than a factor of four, 4.29 to be exact, on the single variable that decides whether the work you already performed is compensable. The FAR clause is the bluntest of the set: on a federal job, the government does not deny the change, it simply refuses to pay for anything you spent more than 20 days before you put the notice in writing. The work happened. The cost was real. The clause erases it anyway.

Section 7.4.1 makes the point again. In the AIA text, if the contractor performs an architect's minor change order without first giving notice that it affects the Contract Sum or Contract Time, the contractor waives the adjustment. Vanderbilt's 7.4.1 does something else entirely: it requires the contractor to maintain a "Project Issues Log" tracking status, cost, and time impact of every issue needing owner attention. Medford's 7.4.1 goes the other way and states that minor changes entitle the contractor to no additional compensation and no additional time, full stop.

Three contracts, one section number, three unrelated obligations. This is why a downloaded change order log template cannot work off the shelf. The notice period is not a constant you hardcode. It is a per-contract input, and it belongs in a cell.

The Three Dates That Replace Your Status Column

Delete the status column. It is a narrative field in a ledger, and it is the reason the log never fires a warning. Replace it with three dates and one number.

• Event date. The day the condition arose, not the day you wrote it up. Differing site condition uncovered, ASI received, RFI answered in a way that changed the scope.
• Recognition date. The day you could reasonably have known. On a 21 day clause running from the later of the two, this is the field that buys you time, and it is the field you must be able to defend.
• Notice date. The day written notice actually went out, with proof of delivery.
• Notice period. The contract's number of days, entered per project. Seven for the school district, thirty for the housing commission, twenty on the federal job.

One warning before the formulas. Contract days are calendar days unless the contract says otherwise, so do not reach for =WORKDAY() to set the deadline or =NETWORKDAYS() to measure the elapsed time. Both are the natural instinct on a construction schedule and both are wrong on a notice clause. =WORKDAY(D5,7) lands 9 to 11 calendar days out depending on which weekday the event fell on, so it invents a deadline that does not exist and hands you a different wrong answer every time. You will discover the difference in a deposition.

Build the Deadline Engine, Then Re-Read the Log

Layout for the working file. A small header block, then the log starting at row 5.

The deadline itself is one formula, and it has to encode the "whichever is later" trigger rather than assuming the event date:

H5: =MAX(D5,E5)+F5

That is the date after which this change order is worth nothing regardless of its merits. Now the field that should replace "Submitted":

I5: =IF(G5="",IF($B$3>H5,"BARRED","OPEN "&H5-$B$3&"d"),IF(G5<=H5,"PRESERVED","LATE"))

Four states, each a different business decision. PRESERVED means negotiate on the merits. LATE means you are negotiating on the owner's goodwill and should price that in. OPEN with a day count means stop reading and send the notice. BARRED means write it off and go find out who let it happen.

Two roll-ups turn the log into a management tool. The first is the number nobody wants on a screen and everybody needs:

=SUMIFS($C$5:$C$60,$I$5:$I$60,"LATE")+SUMIFS($C$5:$C$60,$I$5:$I$60,"BARRED")

The second is the one that drives Monday morning:

=SUMPRODUCT((LEFT($I$5:$I$60,4)="OPEN")*($H$5:$H$60-$B$3<=3)*$C$5:$C$60)

That is the dollar value of change orders whose notice window closes within three days. Put it in a cell at the top of the sheet in 24 point type. Add a conditional formatting rule across the row with the formula =AND($G5="",$H5-$B$3<=3) so the row turns red before the money evaporates instead of after.

The same eight rows, with the dates the log was missing

Same eight change orders, now with the dates the log was missing. Notice period is 7 days, run date September 17, 2026.

$54,550 of the $197,500 was noticed late, which is 27.62 percent of the open log. Another $25,800, or 13.06 percent, is sitting in an open window that closes in one day and four days. The status column called all eight of them "Submitted," "Pending," or "In progress."

Compute Your Own Aging Curve, Do Not Borrow One

A change order is not worth its face value. It is worth face value times the share you historically collect, and that share falls with age and collapses when notice was late. The contractor in this example pulled his own closed change orders from the last three years and computed the realization rate by age bucket, using nothing more than two SUMIFS calls per bucket:

=SUMIFS(hist_approved,hist_age,">="&$Q5,hist_age,"<="&$R5)/SUMIFS(hist_requested,hist_age,">="&$Q5,hist_age,"<="&$R5)

Those five numbers are that contractor's history, not an industry benchmark, and you should not copy them. The point is the method. Pull your closed log, bucket by age at settlement, divide approved by requested, and you will have a curve that is specific to your owners, your architects and your paperwork. Then value the open log with one formula:

N5: =C5*VLOOKUP(L5,$Q$5:$S$9,3,TRUE)

Where L5 is the age since notice, =IF(K5="",$B$3-G5,""), and K5 is the signed date.

The $55,585 Three Date Columns Were Worth

Run the same eight change orders twice. First as the status-only log actually managed them: CO-08 and CO-10 noticed late, CO-12 and CO-14 never noticed at all because nothing flagged the window. Then as a log with a deadline column would have managed them, with written notice going out two days after every event.

$55,585 of difference on one project, from three date columns, one notice period cell and one IF statement. That is 28.14 percent of the open log, and it is not a pricing improvement. Every change order is priced identically in both columns. The entire gap comes from when the paper moved. If you want the pricing side of the same problem, that is a separate discipline covered in the change order markup calculator, and the two compound: a well priced change order noticed on day 28 of a 7 day window is still worth 34 cents on the dollar.

Pending Change Orders Are a Loan You Made at Zero Percent

There is a second cost, smaller but constant, and almost nobody puts it in the log. While a change order sits unsigned, you have already paid the labor, the material and the burden. You financed the owner's change with your line of credit. The bank prime loan rate stood at 6.75 percent on September 15, 2026 per the Federal Reserve H.15 release, so a contractor borrowing at prime plus two is carrying that work at 8.75 percent.

=C5*$B$2/365*L5

CO-13 alone is 82 percent of the carrying cost, and it burns another $394 for every month it stays unsigned. That is the argument to bring to the owner meeting, and it is more persuasive than "we would like to get these closed out." It is also the reason to bill Construction Change Directive work as it goes rather than waiting for a signature. Under A201 section 7.3.9 the contractor may request payment for work completed under a directive in the monthly application, and the architect makes an interim determination of what is reasonably justified. If your billing treats directed work as unbillable until signed, you are financing it by choice. The mechanics of that are in time and materials billing.

The Monday Morning Check, and What to Do This Week

Run this in four minutes at the start of every week, before the owner meeting, not after it.

• Sort the log by column H, the deadline, ascending. Anything with a blank notice date in the top rows is today's work, not this week's.
• Read the OPEN roll-up cell. If it is not zero, someone sends notice before lunch. A one paragraph letter beats a perfect change order proposal filed on day 8.
• Check that every row's notice period in column F matches this project's contract, not the last project's. Copying last job's file is how the 30 day habit walks onto a 7 day job.
• Look at rows over 60 days since notice. Those are not pending, they are declining, and the aging curve says so.
• Confirm the recognition dates you entered are defensible. A recognition date you cannot support with an email, a daily report or a photo is a date the owner's counsel will move back to the event.
• Reconcile the log total to the pay application. Exposure that never entered the application is exposure nobody upstream knows about.

The events that start these clocks come from the RFI log and the field, so the two files have to agree on dates. If your RFI responses are not date stamped in a way you can pull into column D, fix that first with a proper RFI tracking spreadsheet. The same logic applies to weather, where the notice window is usually shorter still and is documented in the weather delay log. And when a directive tells you to compress the schedule rather than add scope, the cost lives in the acceleration claim, on its own clock.

Open your current contract, find section 15.1.3.1 or its equivalent, and write the number of days on a sticky note. Then open your change order log. If that number does not appear in a cell, your log is a bookkeeping record, not a control. Add four columns today: event date, recognition date, notice period, notice date. Add the deadline formula and the four state flag. You will find at least one dead change order in the first ten minutes, and that discovery is the cheapest one you will ever make, because the alternative is finding it at closeout when the owner's team has the log and you do not have the argument.

Building the deadline engine, the aging curve and the exposure roll-up from a blank sheet is a full day of work, and getting the SUMPRODUCT and the VLOOKUP ranges right takes another. The SheetCraft Construction Budget Tracker ships with the change order module already wired: per contract notice periods, the four state notice flag, the aging buckets with your own realization rates, the carrying cost line, and the roll-up that ties committed and pending change orders back to the budget and the pay application, so the number you quote at the owner meeting is the same number your accountant sees. If you are managing more than three open change orders on a job with a seven day notice clause, the template pays for itself the first time one row turns red.