Developer ToolsFree generatorPDF, Excel, Word export

Excel Open Order Aging and Late Line Formula Generator

Most order desks still live in an Excel export from the ERP. These formulas turn a plain open order report into an aging view and an on-time delivery measure without macros.

Fill in

Column with customer due or promise date

Column with actual ship date

Column with quantity ordered

Column with quantity shipped

First data row

On-time grace period (days)

First empty output column

The formulas below go in this and the next columns, one per column.

Generated code

Download:

Open quantity (put in K2 and fill down)

=MAX(H2-I2,0)

Days past due for open lines (next column)

=IF(AND(ISNUMBER(F2),H2-I2>0),MAX(TODAY()-F2,0),0)

Aging bucket (next column, refers to days past due)

=IF(L2=0,"Not late",IF(L2<=7,"1-7 days",IF(L2<=14,"8-14 days",IF(L2<=30,"15-30 days","Over 30 days"))))

On-time flag for shipped lines (next column)

=IF(ISNUMBER(G2),IF(G2<=F2+0,1,0),"")

Summary formulas (put anywhere, adjust the last row)

On-time delivery %: =SUM(N2:N5000)/COUNT(N2:N5000)
Open lines: =COUNTIF(K2:K5000,">0")
Lines over 30 days late: =COUNTIF(M2:M5000,"Over 30 days")
Pivot tip: use the aging bucket as rows and the count of lines as values.

The formulas assume {{out_col}} is column K, so days past due is column L, the bucket is M and the on-time flag is N. If you choose another output column, shift the L, M and N references to match. Dates must be real Excel dates, not text. Rounding and time zone effects in exports can shift a day, so check a sample row.

Get this report live from your ERP

Optional: we can look at replacing the weekly export with a read-only live view.

Optional. The tool and every export work without this. No spam.

How to use it

Enter the column letters from your export for due date, ship date, ordered and shipped quantity, plus the first data row. Copy each formula into the first row of its column and fill down. Convert the range to an Excel table if you want formulas to extend automatically when you paste new data.

What each formula does

Open quantity is ordered less shipped, floored at zero for over-shipments. Days past due is today minus the due date, only for lines that still have open quantity. The bucket groups days past due into the ranges most customer service reviews use. The on-time flag compares the actual ship date with the due date plus any grace days your customer agreement allows.

Reading the output

A pivot table with the aging bucket in rows and a count of lines in values shows how deep the problem is. Lines over 30 days late usually point to material shortages or credit holds, not scheduling noise. On-time delivery based on ship date is not the same as delivery date on the customer dock, so state which one you report.

Common mistakes

Text dates are the top cause of wrong results, so check that the cells right-align. Others are measuring against the original request date instead of the current promise date, counting service lines with no shipment, and forgetting to exclude cancelled lines. Agree the date definition with sales before the report goes into a review.

Frequently Asked Questions

How do I calculate days past due in Excel?

Subtract the due date from TODAY() and floor it at zero, for example =MAX(TODAY()-F2,0). Apply it only to lines with open quantity so shipped lines do not keep aging.

How do I make aging buckets in Excel?

Use nested IF statements on the days past due, or a lookup table with LOOKUP or XLOOKUP. The generator uses nested IF so it works in every Excel version.

What is a good on-time delivery grace period?

It depends on the customer agreement. Some contracts allow zero days, others a window such as a few days early or late. Use the number written in the agreement and keep it identical in every report.

Will this work with Google Sheets?

Yes. These functions exist in Google Sheets with the same syntax. Make sure the date columns are recognized as dates after you import the export.

If you would rather get this report live from your ERP without exports, we can help build it with read-only access. The formulas work on their own.

Talk to an Expert Explore More Free Tools

Related Tools

ERP Operations

More Developer Tools tools

See all

Back to All Tools