Construction Long Lead Item Tracker in Excel: Find the Eight Week Hole in Month One

A general contractor in Columbus took a $8,400,000 medical office building last spring. Thirty eight thousand square feet, two stories, notice to proceed on Monday March 2, 2026, substantial completion March 5, 2027. The CPM schedule was 340 activities and looked professional. It showed the main switchgear being set in late December with a four day activity bar and two weeks of float. What it did not show is that the switchgear had a 40 week quoted lead time, which meant the purchase order had to reach the factory on March 3, one day after the job started, and the subcontract that authorized that purchase order had to be signed on January 2, eight weeks before the owner had even issued notice to proceed. A construction long lead item tracker Excel workbook exists to put that January 2 date on a page in week one, while there are still six or seven levers to pull, instead of in month seven when the electrician calls and asks where his gear is.

The float on that one item was negative 8.4 weeks on day one. At $10,600 a week of extended general conditions and $1,850 a day of liquidated damages, a critical path week on that job costs $23,550. The switchgear was carrying $197,800 of exposure before a footing was poured, and nothing in the schedule, the budget, or the submittal log said so.

The Gantt Chart Plans the Install. The Vendor Controls the Release.

A CPM schedule models work: excavate, form, pour, erect, set, connect. Long lead equipment is not work. It is a chain of paperwork gates that has to finish before any work can start, and every gate in that chain sits with somebody who does not report to you. The subcontractor prepares the submittal. The engineer of record reviews it. Your own accounting department issues the purchase order. The factory schedules production against a backlog you cannot see. By the time the item appears as a bar on your schedule, every decision that determined whether it arrives has already been made or missed.

So the tracker is not a schedule. It is a deadline generator. You take the date the item has to be on site, subtract every duration in the chain, and read off the date each gate has to close. Here is what that produced on the Columbus job when the project manager ran it in week one, sorted by how much trouble each item was in.

Three items were already late on the first morning. That is not a failure of this particular contractor. It is the normal condition of a job where the equipment lead time exceeds the buyout window, and it is invisible until somebody does the subtraction. Notice also that the generator has a longer quoted lead than the rooftop units, 34 weeks against 26, and is in far better shape. Lead time alone tells you nothing. Lead time measured against the need date is the only ranking that means anything, which is why a list of items sorted by weeks of lead is a decoration and a list sorted by float is a work list.

Which items belong on the tracker

Put an item on the list if the fab and ship duration is 12 weeks or more, if it is engineered to order, or if it is single sourced at any lead time. That usually gives you 8 to 15 rows on a mid size commercial job. If your list has 40 rows because somebody added door hardware and toilet partitions, nobody will update it past month two, and an abandoned tracker is worse than no tracker because it looks like coverage.

Build Every Chain Backward From the Need Date

One row per item. Headers in row 3, data from row 4 down. Columns D through I are the inputs you type. Columns J through M compute themselves and are the deadlines you manage to.

The four backward formulas, starting in row 4:

Release by (M4), the date the approved submittal has to be at the factory:
=D4-(E4+F4)*7

Approve by (L4):
=WORKDAY(M4,-G4*5,Holidays)

Submit by (K4):
=WORKDAY(L4,-H4*5,Holidays)

Award by (J4):
=WORKDAY(K4,-I4*5,Holidays)

The mix of plain subtraction and WORKDAY is deliberate. Fabrication runs on calendar time. A factory in Wisconsin building your switchgear does not stop for Presidents Day, so column F gets multiplied by 7 and subtracted straight. The paperwork chain runs on business days, and the engineer of record absolutely does stop for Thanksgiving. Name a range of federal holidays plus your own shutdown days Holidays and feed it to every WORKDAY call. On the Columbus job that single argument moved the switchgear award by date from January 6 to January 2, four days that a naive chain would have quietly given away.

Put the factory shutdown in column F, not in a note

The most expensive weeks in procurement are the ones nobody quotes. European extruders shut down for most of August. Many domestic equipment plants close the week between Christmas and New Year, and some take the first week of July. A curtain wall package quoted at 16 weeks that crosses a four week August shutdown is a 20 week package. Add the shutdown weeks directly into column F and write the reason in a cell comment. If you keep shutdown weeks in a separate column you will forget to add them, because the formula will not.

The Float Column Is the Only Column That Matters

Float has to answer one question: as of the last thing that actually happened, are we ahead or behind? That means the formula walks the chain from the far end backward, finds the most advanced gate that has a real date in it, and measures that date against its own deadline. In S4:

=IFS(R4<>"",(D4-R4)/7, Q4<>"",(M4-Q4)/7, P4<>"",(L4-P4)/7, O4<>"",(K4-O4)/7, N4<>"",(J4-N4)/7, TRUE,(J4-TODAY())/7)

Read it right to left and it makes sense. If the vendor has confirmed delivery, float is the cushion between that confirmed date and the need date. If not, but the purchase order has been released, float is how early or late the release hit against the release by date. If nothing at all has happened, float is the runway left before the award deadline, measured against today, which means it decays by exactly one week every week you do nothing. That last clause is the whole value of the tool. An item with nine weeks of float in March has negative two weeks of float in June if you never touch it.

On Excel 2016 and older, IFS does not exist. The nested version behaves identically:

=IF(R4<>"",(D4-R4)/7,IF(Q4<>"",(M4-Q4)/7,IF(P4<>"",(L4-P4)/7,IF(O4<>"",(K4-O4)/7,IF(N4<>"",(J4-N4)/7,(J4-TODAY())/7)))))

Status in T4, four bands, sized so that "critical" means you have less than a normal review cycle of cushion left:

=IF(R4<>"","LOCKED",IF(S4<0,"LATE "&TEXT(-S4,"0.0")&" WK",IF(S4<2,"CRITICAL",IF(S4<6,"WATCH","OK"))))

Convert the worst float into a dollar figure

A red cell gets ignored. A dollar figure gets escalated. Put your weekly cost of delay in B2 and build a three cell summary block above the table:

B2 on this job held $23,550, which is $10,600 of weekly general conditions plus $12,950 of weekly liquidated damages. That number is not precise and does not need to be. It needs to be defensible enough to survive the sentence "our switchgear position is worth about $198,000 of risk and I need a decision on it this week."

What a 40 Week Lead Time Quote Actually Hides

Four things, and every one of them has cost somebody a certificate of occupancy.

The clock starts at approved submittal, not at purchase order. Almost every equipment quote reads "weeks from receipt of approved submittal" or "from release to fabrication." Contractors routinely count 40 weeks from the day they sign the subcontract, which buries the entire submittal and review chain inside the lead time instead of ahead of it. On this job that mistake was worth 8 work weeks, the difference between an award by date of January 2 and one in late February.

Freight is not in the quote. Ex works pricing ends at the factory dock. A switchgear lineup out of the Midwest is another 2 weeks on the road, and an oversize load needs permits and a route survey. Put freight in column F. If the quote says FOB job site, confirm it in writing, because purchasing departments and shipping departments disagree about this constantly.

The quote expires. A 40 week lead quoted in January is frequently 46 weeks by April, and the vendor is under no obligation to tell you. Reconfirm lead time every 30 days until the release date, and stamp the date you confirmed it. If the confirmed lead grows, column F changes and every downstream deadline recomputes on its own, which is the entire reason the dates are formulas and not typed values.

First pass approval is not the base case. Major equipment submittals get rejected or returned "revise and resubmit" often enough that planning on one review cycle is planning on luck. Carry two cycles in column H for anything over 20 weeks of lead. It costs you three weeks of apparent float on paper and saves you from discovering the shortfall after the rejection.

Recovering Eight Weeks Without Paying for Eight Weeks

Finding negative 8.4 weeks in week one is only worth something if you know what to do with it. The levers are not equal, and they compress different gates, so they add.

The first five levers together recover 7 of the 8.4 weeks and cost $2,500. The sixth recovers all of it by itself and costs $22,000. The comparison the project manager took to the owner was one line: $22,000 against $197,800 of exposure, on a package where the alternate was an approved equal already listed in the specification. The owner approved it in a day.

With the alternate manufacturer at 26 weeks, a one week buffer, and the compressed paperwork chain, the switchgear release by date moved from March 3 to June 16 and its float went from negative 8.4 weeks to positive 12.1. The same compression applied to the rooftop units took them from negative 4.0 to positive 0.1, and to the structural steel from negative 4.0 to negative 0.9, close enough to absorb with a two week erection resequence. Three fires, one week, no delay claim.

The week 12 review catches what week one missed

Here is the same tracker on Friday May 22, 2026, with actuals typed in and the float formula walking each chain to its most advanced closed gate.

Look at the fire pump. On day one it had 9.0 weeks of float and it was the second healthiest item on the list. Nobody touched it for eleven weeks while the team fought the three fires at the top of the table, and float decays at one week per week. It is now 2.6 weeks late on award, and the item it holds up is not a wall or a ceiling, it is the fire protection system that gates the certificate of occupancy. That is the failure mode of every long lead process that is not reviewed on a schedule: the loud items get managed and the quiet ones go negative in silence.

The routine that prevents it takes fifteen minutes. Every Friday, sort the tracker by column S ascending, type in any gate that closed during the week, and read the top three rows out loud in the Monday coordination meeting. Anything in LATE or CRITICAL gets a name and a date attached before the meeting ends. Anything in WATCH gets a lead time reconfirmation email to the vendor if the last one is more than 30 days old. That is the entire process.

Start With the Three Items That Can Actually Hurt You

You do not need 15 rows on Monday. Take the three items on your current job with the longest quoted lead times, get their real need on site dates out of the schedule, and run the four backward formulas. The exercise takes twenty minutes and one of two things happens. Either every award by date is in the future, in which case you have three deadlines you did not have this morning, or one of them is in the past, in which case you have found a problem while it still has six cheap answers instead of one expensive one.

The tracker only works if it sits next to the money. A release date that slips is a general conditions cost, an alternate manufacturer is a budget variance, and an expedited review fee is a line item somebody has to approve. The SheetCraft Construction Budget Tracker ships with the long lead schedule already wired into the cost side of the workbook, so the four backward dates, the float calculation, the status bands, and the weekly cost of delay all live in the same file as your budget, commitments, and change orders. The formulas above are built and tested, the holiday range is populated, and the summary block reports exposure in dollars the first time you type a need date. You can build this yourself in an afternoon, and you should if you have the afternoon. If you would rather spend that afternoon getting the switchgear released, start from the template.