# Excel Open Order Aging and Late Line Formula Generator

> Source: https://netray.co/tools/excel-order-aging-and-late-line-formula-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Tell it which columns hold your due date, quantities and ship date, and it writes the Excel formulas for days past due, aging buckets, open quantity and OTD flags.

## Category

developer

## Audience

- Customer service
- Order desk
- Sales operations

## Tool

**Kind:** template

**Output:** code

**Language:** text

**Fields:**

#### Column with customer due or promise date

**Key:** due_col

**Type:** text

**Default:** F

#### Column with actual ship date

**Key:** ship_col

**Type:** text

**Default:** G

#### Column with quantity ordered

**Key:** ord_col

**Type:** text

**Default:** H

#### Column with quantity shipped

**Key:** shp_col

**Type:** text

**Default:** I

#### First data row

**Key:** first_row

**Type:** number

**Default:** 2

#### On-time grace period (days)

**Key:** grace

**Type:** number

**Default:** 0

#### First empty output column

**Key:** out_col

**Type:** text

**Default:** K

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

**Blocks:**

#### Open quantity (put in {{out_col}}{{first_row}} and fill down)

**Body:** =MAX({{ord_col}}{{first_row}}-{{shp_col}}{{first_row}},0)

#### Days past due for open lines (next column)

**Body:** =IF(AND(ISNUMBER({{due_col}}{{first_row}}),{{ord_col}}{{first_row}}-{{shp_col}}{{first_row}}>0),MAX(TODAY()-{{due_col}}{{first_row}},0),0)

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

**Body:** =IF(L{{first_row}}=0,"Not late",IF(L{{first_row}}<=7,"1-7 days",IF(L{{first_row}}<=14,"8-14 days",IF(L{{first_row}}<=30,"15-30 days","Over 30 days"))))

#### On-time flag for shipped lines (next column)

**Body:** =IF(ISNUMBER({{ship_col}}{{first_row}}),IF({{ship_col}}{{first_row}}<={{due_col}}{{first_row}}+{{grace}},1,0),"")

#### Summary formulas (put anywhere, adjust the last row)

**Body:** On-time delivery %: =SUM(N{{first_row}}:N5000)/COUNT(N{{first_row}}:N5000)
Open lines: =COUNTIF({{out_col}}{{first_row}}:{{out_col}}5000,">0")
Lines over 30 days late: =COUNTIF(M{{first_row}}:M5000,"Over 30 days")
Pivot tip: use the aging bucket as rows and the count of lines as values.

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### How do I calculate days past due in Excel?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

excel formula days past due, open order aging excel, excel aging buckets formula, late orders report excel, excel on time delivery formula, backorder aging spreadsheet formulas, excel open order report from erp export

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json