Most teams can tell you their contractual review period. Very few can tell you how long their reviews actually take, how many cycles a typical document needs, or whether the delay sits with the authors, the reviewers, or the inbox in between. The data to answer all three is usually already in the comment resolution sheet. It just isn't being added up.
This guide shows how to turn a CRS and a simple review log into eight cycle-time metrics, with Excel formulas you can paste in. For what "good" looks like once you have the numbers, see How Long Should a Design Review Take?
Cycle time exists at two levels, and you need both:
| Level | Question it answers | Where the data lives |
|---|---|---|
| Comment | How long does one comment take to go from raised to closed, and where does it wait? | The CRS: one row per comment |
| Document | How long does each review round trip take, and how many does a document need before approval? | A review log: one row per transmittal |
Comment-level metrics tell you why reviews are slow. Document-level metrics tell you how much it costs the schedule. A project can close comments quickly and still lose months to a third and fourth cycle, so measuring only one level gives a misleading picture.
Our free CRS template for Excel already records Date Raised (column I) and Status (column L). To measure cycle time you need two more dates, kept as real dates rather than text:
| Column | Header | What goes in it |
|---|---|---|
| I | Date Raised | Already in the template |
| N | Date Responded | The date the author's response was entered |
| O | Date Closed | The date the reviewer verified and closed the comment |
If you use the template's combined Closed By / Date column (M), keep the name there and move the date into column O. A date typed inside a text cell ("J. Weber, 2026-07-15") cannot be calculated with.
The formulas below assume data in rows 2 to 500. Adjust the ranges to fit your sheet.
Add these as helper columns on the CRS sheet.
1. Comment closure time (raised → closed), column P:
=IF(O2="","",O2-I2)
Summarise it with the median, not the average. One comment stuck for three months will drag an average far away from the typical case:
=MEDIAN(P2:P500)
MEDIAN skips the blank results returned for open comments, so only closed comments are counted.
2. Author response time (raised → responded), column Q:
=IF(N2="","",N2-I2)
3. Reviewer verification time (responded → closed), column R:
=IF(OR(N2="",O2=""),"",O2-N2)
Metrics 2 and 3 are the split that matters. Closure time tells you a comment took 30 days; these two tell you whether 25 of those days were the author's or the reviewer's. A long verification time is the signature of comments sitting in Answered while nobody checks the revised document.
4. Open comment age, column S:
=IF(OR(L2="Closed",I2=""),"",TODAY()-I2)
The oldest open comment on the project:
=MAX(S2:S500)
How many open comments are older than 14 days (change the 14 to your contractual review period):
=COUNTIFS(L2:L500,"<>Closed",I2:I500,"<"&TODAY()-14)
The CRS records comments, not transmittals. For document-level numbers, keep a second sheet named Review Log, with one row each time a document revision goes out for review:
| Column | Header | Example |
|---|---|---|
| A | Document Number | DWG-ME-1204 |
| B | Revision | Rev B |
| C | Date Issued | 2026-07-03 |
| D | Date Returned | 2026-07-17 |
| E | Review Code | B |
| F | Cycle # | (formula below) |
Sort it by document number and then date issued, or simply add rows in the order the transmittals go out.
5. Review cycle duration (issued → returned) for each row:
=IF(D2="","",D2-C2)
6. Cycle number, column F: a running count of how many times that document has been reviewed so far:
=COUNTIF($A$2:A2,A2)
The first review of a document gets 1, its resubmission gets 2, and so on. This one column makes the next two metrics simple.
7. Average cycles per document:
=COUNTA(A2:A500)/COUNTIF(F2:F500,1)
Total reviews divided by the number of distinct documents, because each document has exactly one row where the cycle number is 1. Put this and the next formula on a separate summary sheet, or at least outside columns A to F: a label typed into column A would be counted as another review.
8. First-pass approval rate: the share of documents approved (code A or B) on their first review:
=(COUNTIFS(F2:F500,1,E2:E500,"A")+COUNTIFS(F2:F500,1,E2:E500,"B"))/COUNTIF(F2:F500,1)
If your project uses different codes, such as ISO 19650 suitability codes or "Approved / Approved as Noted", change "A" and "B" to whatever counts as approval. The codes themselves are explained in Document Review Codes Explained.
One more check belongs here: comments still open when a document was approved. For a given document and revision, count the CRS rows that are not closed:
=COUNTIFS(CRS!B2:B500,A2,CRS!C2:C500,B2,CRS!L2:L500,"<>Closed")
Any result above zero on an approved revision is a comment that was waved through, not resolved.
The metrics are most useful in combination. Each pattern points to a different cause:
| What you see | What it usually means | Where to look |
|---|---|---|
| Long author response time | Comments are unclear, or authors are overloaded | Comment quality: are comments written as verifiable requirements? |
| Long reviewer verification time | Comments sit in Answered and nobody checks the revision | Who owns verification, and whether anyone is chasing it |
| Review cycle duration near the contractual limit every time | The review waits in a queue, then is done at the deadline | Elapsed time versus effort; automatic reminders |
| High cycles per document, short cycles | Each review is fast but does not settle the document | Re-reviewing from scratch; incomplete packages at intake |
| Low first-pass approval rate | The problem is upstream of the review | Package completeness and an unclear review basis |
| Open comments on approved documents | Approval is not tied to closure | Whether the close-out step is enforced |
The fixes for each of these are covered in How Long Should a Design Review Take? and 7 Mistakes You're Making with Manual CRS Workflows.
These formulas are only as good as the dates behind them, and a spreadsheet makes those dates easy to get wrong:
None of this makes the spreadsheet useless. A rough median from your own project beats a precise number nobody has. But it is why the numbers should be read as trends, not exact figures.
When comments live in a structured system, every one of these metrics falls out of the review itself. Contrat.io tracks comment age, closure time, open counts per document, and overdue reviews as a by-product of running the review. The timestamps are recorded when things happen, not when someone remembers to type them, and open comments carry forward between revisions with their original IDs and dates intact. Try it free for 30 days, no credit card required, and compare its numbers against the ones from your spreadsheet.
At two levels. For each comment, measure the days from raised to closed, split into author response time and reviewer verification time. For each document, measure the days from issue to return for every review, and count how many reviews it needs before approval.
Review cycle time is how long one round trip takes, from transmittal to returned comments. Cycles per document is how many round trips a document needs before approval. The second usually matters more to the schedule: three on-time cycles take longer than one slow one.
The median. A handful of comments stuck for months will pull the average far above what is typical, while the median shows the experience of most comments. Track the oldest open comment separately so the stuck ones are not hidden.
Number each review of a document with a running count (=COUNTIF($A$2:A2,A2)), then divide the number of first reviews returned with an approval code by the total number of first reviews.
A CRS with three dates per comment (raised, responded, closed) and a review log with the issue date, return date, and review code of every transmittal. Most projects already have this information somewhere; it just needs to be in date columns.