Deriving bridge inspection intervals when the spreadsheet has outgrown its owner

Why a bridge programme outgrows the workbook faster than a plant programme

A fixed-equipment programme has a few hundred assets and essentially one arithmetic: measured wall, minimum thickness, corrosion rate, next date. A bridge portfolio has hundreds or thousands of structures, each carrying more than one clock at once. A single steel through-truss can hold a routine interval, an NSTM interval, an underwater interval on its piers, a monitoring cycle on a fatigue-prone detail and a load rating that changes the moment a condition state drops. Multiply that across a portfolio and the workbook is no longer a list of dates. It is an undocumented application with several authors.

The tipping point is rarely volume. It is the second editor. One person maintaining a workbook holds the rules in their head and the file works because they are the interpreter. Once a second and third person edit it, the file has to state its own rules, and it never does. Sorting a sheet breaks a lookup that referenced row position. A copied formula picks up a relative reference nobody notices for three cycles. Someone hard-codes a date to close out an overdue item during an audit sprint and the formula is gone forever underneath a number that looks correct.

The symptom people describe is that nobody can reproduce a calculation. The underlying condition is that the file records outputs and not derivations. Every number in it is defensible on the day it is typed and indefensible six months later, because the evidence for it was the person who typed it.

The interval rules the engine actually has to encode

Since the 2022 revision of 23 CFR 650 Subpart C, routine inspection intervals are assigned by one of two methods. Method 1 works from a 24-month baseline, with defined criteria that allow extension to 48 months or require shortening to 12. Method 2 is a risk-based inspection method under which an agency's FHWA-accepted procedure can support intervals out to 72 months. Nonredundant steel tension member inspections and underwater inspections run on their own clocks — typically 24 and 60 months respectively at baseline — and are not satisfied by the routine inspection happening on time.

The regulation also allows a tolerance period around the assigned interval. This is the part that a spreadsheet handles worst, because a tolerance is not slack. It is an exception that has to be recorded and justified. In a workbook, tolerance becomes a habit: items drift to the back of the window every cycle until the drift consumes the tolerance and the first genuinely late inspection appears as a compliance finding rather than as a trend anyone saw coming. An interval engine should show the distribution of completion dates within the window, not just a red or green flag on the deadline.

There is a second regulatory consequence that plant-side software never has to model. Bridge inspection compliance is assessed against FHWA metrics on the agency's whole programme, and the data submitted to the National Bridge Inventory under the SNBI specifications has to agree with the inspection record. A due date the field team can defend and a data submittal that says something different is still a finding.

Remaining life on a bridge is not wall loss divided by corrosion rate

Ask a vendor selling a remaining-life module what remaining life means and you will usually get thickness arithmetic. That answer is correct for a pressure vessel and close to meaningless for a bridge. On a steel superstructure, remaining life is dominated by fatigue: a finite-life estimate driven by the stress range at a detail, the number of cycles it has accumulated, and the detail category assigned to it under the AASHTO LRFD fatigue provisions. Two members on the same span, in identical condition, can have remaining lives an order of magnitude apart because one is a Category C detail and the other is Category E prime.

On a concrete structure, the driver is usually chloride ingress and the resulting reinforcement corrosion, evidenced not by a wall thickness but by half-cell potential mapping under ASTM C876, delamination sounding under ASTM D4580, and ground penetrating radar under ASTM D6087 on decks with overlays. Post-tensioned structures add tendon and duct condition, where the damage is concentrated, hidden and poorly correlated with the surface condition an inspector can rate.

The third input is load rating under the AASHTO Manual for Bridge Evaluation, which is what actually determines whether a structure stays open. A rating factor below one means a posting or a restriction, and rating is sensitive to section loss and condition state. An interval engine that treats all three of these as free-text notes has not modelled remaining life. It has modelled a comment field.

Four arithmetic traps that live comfortably in a shared workbook

The first is date precision. Inventory data records inspections by month and year, while a workbook does arithmetic in days. Adding 24 months to a date field and then comparing it to a month-level record produces off-by-one disputes at the boundary of every window, and those disputes cluster exactly where the tolerance matters. Decide whether the programme runs on month arithmetic or day arithmetic and make the engine enforce one of them everywhere.

The second is the anchor. Anchoring the next due date on the actual completion date rather than on the assigned due date means every late inspection permanently shifts the whole cycle. Run that across five cycles on a 24-month clock with an average three-month slip and the structure has quietly lost most of an inspection. Anchoring on the assigned due date preserves cadence but compresses the interval after a slip, so the engine must be explicit about which convention it uses and must show the compression rather than hide it.

The third is element quantity. Under the AASHTO MBEI, the quantities distributed across condition states for an element must sum to the total quantity of that element. A spreadsheet will happily accept a distribution that sums to 94 per cent or 108 per cent, and the error propagates into the deterioration trend and from there into any risk-based interval. The fourth trap is the stale assessment: a Method 2 interval assigned when the structure was in good condition, still running years after the condition data that justified it moved. All four survive in a workbook precisely because a workbook validates nothing.

What an unreproducible calculation actually costs

The visible cost arrives during an FHWA compliance review, when the question is not whether the inspection happened but whether the programme demonstrably follows its own accepted procedures. A due date you cannot derive is a procedure you cannot demonstrate. The reviewer does not need to prove the date is wrong; they need only establish that the agency cannot show how it was produced.

The larger exposure is discovery. After an incident, the inspection record becomes evidence, and a shared workbook is a poor witness. It shows a value with no author, no timestamp and no input set, and it shows a change history that either does not exist or shows edits by people who no longer work there. An engine that stores every derivation as an immutable record with a named approver converts the same question into a routine retrieval.

There is a quieter cost that shows up long before either of those. Staff turnover. When the person who understood the workbook leaves, the programme does not lose a file, it loses the interpreter of a file. Successors respond rationally by rebuilding their own version, and within two cycles the agency is running two sets of dates that disagree in the margins.

How field findings should reach the interval

The NDT that supports a bridge programme looks nothing like a plant thickness survey. Magnetic particle and penetrant testing are applied arms-length to welded details on nonredundant tension members. Ultrasonics are used on pin and hanger assemblies and on anchor rods where the failure surface is inaccessible. Cable-stayed and suspension structures bring in acoustic emission monitoring and specialist strand inspection. Decks bring in the ASTM methods above, plus chain drag and impact echo. Each of these produces a finding whose consequence is a condition state or a defect flag, not a number to plot on a wall-loss chart.

The chain has to be continuous: field result to element condition state, condition state to deterioration trend and risk assessment, risk assessment to assigned interval. In practice this is where migrated workbooks break, because the field result lives in a report PDF and the condition state lives in a cell that someone transcribed. Transcription is where the reproducibility dies, and it is invisible until someone asks how a rating was arrived at.

A useful acceptance test during evaluation: take a real structure, change one element's condition-state distribution in the field record, and watch what happens. If the risk assessment and the assigned interval do not move, or move without leaving a record of why, the chain is decorative.

Moving off the workbook without losing the history

The instinct in a migration is to clean the data first. That is the wrong order. Import the workbook's history as immutable records exactly as it stands, including the values you suspect are wrong, then re-run the rule engine against the same inputs and produce a reconciliation report of every disagreement. Silent correction destroys the only evidence of what the programme actually did, and the disagreements are usually the most valuable output of the whole exercise.

Expect three categories in that report. Dates that agree, which need nothing. Dates that disagree because the workbook applied a rule inconsistently, which need a documented decision on which one stands. And dates the engine cannot derive at all because an input is missing — the qualifying criteria behind a 48-month assignment, the assessment behind a Method 2 interval, the NSTM member list behind a member-level clock. That third category is the real backlog, and it is worth knowing its size before committing to a schedule.

Evaluate on that basis. Ask a vendor to run your own extract through their engine and hand back the reconciliation, not a demo dataset. Ask how rule versions are stored and what happens to historical records when a regulation changes. Ask who can override a derived date, what justification is compulsory, and whether the override is visible on the structure's timeline forever. To see this run against your own bridge and structure inventory, request a working session — bring one year of records and an interval you cannot currently explain.

Request a consultation