Aging Formula in Excel (30, 60, 90 Days): Build a Complete Accounts Receivable Aging Report

If you manage invoices in Excel, there comes a point where simply listing invoice numbers, dates, and amounts is not enough.

You need to know which invoices are overdue, how long they have been overdue, which customers require follow-up, and how much money is tied up in each aging category.

That is exactly what an aging report does.

An aging report groups outstanding invoices into time-based categories, usually 0–30 days, 31–60 days, 61–90 days, and over 90 days. Instead of scanning hundreds or thousands of invoices manually, you can quickly see where your receivables stand and which balances require attention.

In this guide, you will learn how to create an aging formula in Excel and use it to build a complete accounts receivable aging report.

We will cover:

• How to calculate days outstanding
• How to create 30, 60, and 90 day aging buckets
• How to use IF, IFS, and INDEX MATCH
• How to build a customer aging summary
• How to summarize aging data with Pivot Tables
• How to create formula-based reports with SUMIFS
• How to highlight overdue invoices
• How to automate the process with Power Query
• Common mistakes that cause aging reports to break

By the end, you will have a practical structure that can be used for real invoice tracking, accounts receivable reporting, and collections analysis.

What Is an Aging Report?

An aging report is a financial report that groups unpaid invoices based on how long they have been outstanding.

A typical accounts receivable aging report uses categories like this:

Days Outstanding	Aging Bucket
0–30 days	Current
31–60 days	31–60 Days
61–90 days	61–90 Days
91+ days	90+ Days

This allows finance teams, accountants, bookkeepers, and business owners to quickly understand the quality of their receivables.

For example, if a company has $80,000 in unpaid invoices, the total alone does not tell the full story.

Aging report:

Aging Bucket	Amount
Current	$52,000
31–60 Days	$14,000
61–90 Days	$8,000
90+ Days	$6,000

Now the picture is much clearer.

Most of the balance is still current, but $14,000 is already more than 30 days overdue, and $14,000 has aged beyond 60 days. That is the part of the receivables balance that probably deserves closer attention.

Aging reports are commonly used to:

• Prioritize collection efforts
• Monitor customer payment behavior
• Identify overdue balances
• Estimate credit risk
• Support cash flow planning
• Review customer credit limits
• Prepare management reports

In other words, an aging report is not just an Excel exercise. It is a practical finance tool.

Invoice Age vs Days Overdue

Before writing formulas, you need to decide what you are measuring.

There are two common calculations:

1. Invoice age
2. Days overdue

They are related, but they are not the same.

Assume an invoice was issued on January 1 with Net 30 payment terms.

Invoice Date	Terms	Due Date
Jan 1	Net 30	Jan 31

If today is February 10:

• Invoice age = 40 days
• Days overdue = 10 days

Invoice age measures how long it has been since the invoice was issued.

Days overdue measures how long it has been since the invoice became due.

For most accounts receivable aging reports, days overdue is the better metric because it reflects payment risk more accurately.

A customer with Net 60 terms should not be treated as overdue after 30 days simply because the invoice is 30 days old. The invoice is only overdue after the due date passes.

That is why this guide uses Due Date as the main reference point.

If your dataset does not include a due date, you can create one from the invoice date and payment terms.

Example:

=InvoiceDate+30

For Net 45:

=InvoiceDate+45

For Net 60:

=InvoiceDate+60

Once you have a due date, your aging report becomes much more reliable.

Preparing Your Data for an Aging Report

Before creating formulas, your data should be organized properly.

A simple invoice table should include at least these columns:

Column	Description
Invoice No	Unique invoice identifier
Customer	Customer name
Invoice Date	Date the invoice was issued
Due Date	Date the invoice is due
Amount	Outstanding invoice amount
Days Outstanding	Calculated aging days
Aging Bucket	0–30, 31–60, 61–90, 90+

Example:

Before writing any formula, make sure:

• Dates are stored as real Excel dates
• Amounts are stored as numbers
• Blank rows are removed
• Column names are clear
• The data is converted into an Excel Table

To convert the range into an Excel Table:

1. Select the dataset.
2. Press Ctrl + T.
3. Confirm that “My table has headers” is checked.
4. Click OK.

Using an Excel Table makes your report easier to maintain because formulas automatically fill down when new rows are added.

If your table is named Invoices, you can also use structured references such as:

=[@[Due Date]]

instead of regular cell references like:

=E4

This makes formulas easier to read and reduces mistakes in larger reports.

How to Calculate Days Outstanding in Excel

The core aging formula in Excel is simple.

If the due date is in cell E4, use:

=TODAY()-E4

This subtracts the due date from today’s date and returns the number of days outstanding.

Example:

The TODAY function updates automatically, so the report refreshes as time passes.

If you open the workbook tomorrow, the aging days will increase by one.

That is useful for live reports, but it also means your report is dynamic. If you need to preserve aging as of a specific reporting date, use a fixed “Report Date” cell instead.

For example, place the report date in cell B1.

Then use:

=$B$1-E4

This is often better for month-end reporting because the results do not keep changing after the reporting period closes.

Prevent Negative Aging Values

If an invoice is not due yet, the basic formula may return a negative number.

Example:

Due Date	Today	Result
June 25	June 15	-10

In most aging reports, invoices that are not due yet should remain in the Current bucket.

To prevent negative values, use:

=MAX(0,TODAY()-E4)

This formula returns zero if the invoice is not yet due.

Example:

If you are using a report date in B1, use:

=MAX(0,$B$1-E4)

This is one of the safest formulas for aging reports because it prevents negative aging from disrupting your buckets.

Handle Blank Due Dates

Blank due dates can cause misleading results.

If E4 is blank, Excel may treat it as zero and return a very large number of days. That can incorrectly push the invoice into the 90+ bucket.

To avoid this, use:

=IF(E4=””,””,MAX(0,TODAY()-E4))

This formula leaves the result blank if the due date is blank.

If you are using a report date:

=IF(E4=””,””,MAX(0,$B$1-E4))

This version is better for professional reports because it handles three common issues:

• Blank due dates
• Future due dates
• Dynamic aging calculations

How to Create 30-60-90 Aging Buckets

Once you have calculated days outstanding, the next step is to assign each invoice to an aging bucket.

Assume Days Outstanding is in cell G4.

The standard buckets are:

Days Outstanding	Bucket
0–30	Current
31–60	31–60 Days
61–90	61–90 Days
91+	90+ Days

There are several ways to create these buckets in Excel.

The best method depends on your Excel version, your report size, and how often your bucket definitions change.

Aging Formula Using IF

The classic approach uses nested IF statements.

This formula checks each condition in order.

If G4 is less than or equal to 30, it returns Current.

If not, it checks whether G4 is less than or equal to 60.

If not, it checks whether G4 is less than or equal to 90.

Anything above 90 becomes 90+ Days.

Example:

This formula is easy to understand and works in older versions of Excel.

However, it has one weakness: as the number of buckets grows, the formula becomes harder to read.

For example, if you need 0–30, 31–60, 61–90, 91–120, 121–180, and 180+, the nested IF formula becomes long and harder to maintain.

Use IF when:

• Your aging structure is simple
• You only have a few buckets
• You need compatibility with older Excel versions

Aging Formula Using IFS

If you use Excel 2019 or Microsoft 365, the IFS function is cleaner.

IFS checks multiple conditions and returns the result for the first TRUE condition.

This is easier to read than nested IF.

It is also easier to audit because each condition is listed clearly.

Use IFS when:

• You use a modern version of Excel
• You want readable formulas
• Your bucket logic is still relatively simple

For most users, IFS is the best simple solution.

Create Dynamic Aging Buckets Using INDEX MATCH

While IF and IFS formulas work well for simple aging reports, they become harder to maintain as bucket definitions change.

For example, imagine your company decides to replace the standard 30-60-90 structure with:

• Current
• 16-30 Days
• 31-45 Days
• 46-60 Days
• 60+ Days

With IF or IFS, you would need to modify the formula itself.

A more scalable approach is to store aging buckets in a lookup table and use INDEX MATCH.

Create the following bucket table:

Assume:

• Days Outstanding is stored in G4
• Min Days is stored in L4:L7
• Aging Bucket is stored in M4:M7

Formula:

=IF(G4=””,””,INDEX($M$4:$M$7,MATCH(G4,$L$4:$L$7,1)))

Important: This formula uses approximate match (the 1 at the end of MATCH), which requires the Min Days column (L4:L7) to be sorted in ascending order — smallest to largest. If the bucket table is sorted incorrectly, or someone reorders the rows later, MATCH will return the wrong bucket without showing an error. Always double-check that your bucket table is sorted ascending before relying on this formula.

The MATCH function searches for the largest threshold that is less than or equal to the Days Outstanding value.

The INDEX function then returns the corresponding bucket label.

Example:

This approach has several advantages:

• Bucket definitions remain visible and easy to audit.
• Aging categories can be changed without rewriting formulas.
• The logic scales well as new buckets are added.
• It works in virtually all modern versions of Excel.

For long-term maintainability, INDEX MATCH is one of the best methods for assigning aging buckets dynamically.

Build a Complete Accounts Receivable Aging Report

Now let’s combine everything into a practical report.

Your invoice table should look like this:

The two calculated columns are:

Days Outstanding:

=IF([@[Due Date]]=””,””,MAX(0,TODAY()-[@[Due Date]]))

Aging Bucket:

=IF([@[Days Outstanding]]=””,””,INDEX(BucketTable[Aging Bucket],MATCH([@[Days Outstanding]],BucketTable[Min Days],1)))

This structure is clean, scalable, and easy to refresh.

It also gives you a strong foundation for summaries, dashboards, and Pivot Tables.

(remember: BucketTable must stay sorted ascending by Min Days, see note above)

Create Amount-Based Aging Buckets

Many aging tutorials stop after assigning a bucket.

But in real finance work, the amount matters more than the count of invoices.

You do not only want to know that 12 invoices are overdue.

You want to know how much money is overdue.

One way is to create separate amount columns.

Current Amount:

=IF([@[Aging Bucket]]=”Current”,[@Amount],0)

31–60 Amount:

=IF([@[Aging Bucket]]=”31-60 Days”,[@Amount],0)

61–90 Amount:

=IF([@[Aging Bucket]]=”61-90 Days”,[@Amount],0)

90+ Amount:

=IF([@[Aging Bucket]]=”90+ Days”,[@Amount],0)

Your table becomes:

Invoice No	Customer	Amount	Aging Bucket	Current	31-60 Days	61-90 Days	90+ Days
AR-26001	Horizon Logistics	2,450	61-90 Days	0	0	2,450	0
AR-26002	Atlas Construction	8,900	31-60 Days	0	8,900	0	0
AR-26004	BluePeak Energy	12,700	90+ Days	0	0	0	12,700
AR-26007	Nova Retail	3,600	Current	3,600	0	0	0

This layout is useful when exporting to other systems or building simple reports.

However, for flexible analysis, Pivot Tables or SUMIFS are usually better.

Create a Customer Aging Summary

A customer aging summary groups the open balance by customer and bucket.

Example:

Customer	Current	31-60 Days	61-90 Days	90+ Days	Total
Horizon Logistics	0	1,750	2,450	0	4,200
Atlas Construction	0	14,200	0	0	14,200
Meridian Wholesale	0	0	0	4,125	4,125
BluePeak Energy	0	0	0	12,700	12,700
Nova Retail	3,600	0	0	0	3,600

This is much more useful than invoice-level detail for management review.

It quickly shows:

• Which customers owe the most
• Which balances are overdue
• Which customers have the highest risk
• Where collections should focus first

To create this manually, you can use SUMIFS.

Assume:

• Customer column = B4:B13
• Amount column = F4:F13
• Bucket column = H4:H13
• Customer name in the summary = A17
• Bucket headers in the summary = B16:E16

Current:

=SUMIFS($F$4:$F$13,$B$4:$B$13,$A17,$H$4:$H$13,”Current”)

31–60:

=SUMIFS($F$4:$F$13,$B$4:$B$13,$A17,$H$4:$H$13,”31-60 Days”)

61–90:

=SUMIFS($F$4:$F$13,$B$4:$B$13,$A17,$H$4:$H$13,”61-90 Days”)

90+:

=SUMIFS($F$4:$F$13,$B$4:$B$13,$A17,$H$4:$H$13,”90+ Days”)

Total:

=SUM(B17:E17)

Tip: Make sure the bucket labels in your summary headers (B16:E16) match the text returned by your Aging Bucket formula exactly — including spacing. SUMIFS ignores letter case, but extra spaces (often introduced by copy-pasting headers) will cause it to silently return 0 instead of an error. If a bucket total looks wrong, check for trailing spaces with =LEN(B16) and compare it to the expected length.

This gives you a formula-based customer aging report without using Pivot Tables.

While this approach works, most users will find Pivot Tables more flexible because they eliminate the need for separate amount columns.

Summarize Aging Data with Pivot Tables

Pivot Tables are usually the fastest way to summarize aging reports.

To create an aging summary by bucket:

1. Select your invoice table.
2. Go to Insert.
3. Click PivotTable.
4. Add Aging Bucket to Rows.
5. Add Amount to Values.
6. Make sure Amount is summarized by Sum.

The result:

This gives you an instant view of receivables by age.

For a customer-level report:

This creates a customer aging matrix.

This is one of the most useful formats for accounts receivable review.

You can also add filters or slicers for:

• Salesperson
• Region
• Business unit
• Invoice status
• Customer type

This turns the aging report into an interactive analysis tool.

For most finance teams, this Pivot Table becomes the primary working report. It provides a quick overview of receivable exposure while remaining easy to refresh as new invoices are added.

Highlight Overdue Invoices with Conditional Formatting

Conditional formatting makes overdue invoices easier to spot.

To highlight invoices in the 90+ bucket:

1. Select the invoice table.
2. Go to Home.
3. Click Conditional Formatting.
4. Choose New Rule.
5. Select “Use a formula to determine which cells to format.”
6. Enter:

=$H4=”90+ Days”

7. Choose a red fill color.

You can create separate rules for each bucket:

This creates a simple visual heatmap.

It is especially useful when reviewing invoice-level detail with a collections team.

Build an Aging Dashboard in Excel

Once your report is structured properly, you can build a dashboard.

A good aging dashboard should not be complicated. It should answer the most important questions quickly.

Recommended KPI cards:

KPI	Meaning
Total Receivables	Total unpaid balance
Overdue Receivables	Amount above Current bucket
90+ Balance	Highest-risk receivables
Average Days Outstanding	Average age of unpaid invoices
Number of Overdue Customers	Customers with overdue balances
Largest Overdue Customer	Biggest collection priority

Useful charts:

• Aging distribution chart
• Top 10 overdue customers
• Customer aging matrix
• Monthly overdue trend
• 90+ balance trend

For example, an aging distribution chart can show whether most of the receivables balance is current or overdue.

A top overdue customers chart can help collections teams focus on the customers that matter most.

A monthly trend can show whether overdue balances are improving or getting worse over time.

This is where an aging report becomes more than a spreadsheet. It becomes a management tool.

Optional: Summarize Aging Data with SUMIFS

While Pivot Tables are generally the best way to summarize aging reports, some users prefer formula-based reports.

In those cases, SUMIFS can be used to calculate aging balances directly.

For example, to calculate the total balance in the 90+ Days bucket:

=SUMIFS( Invoices[Amount], Invoices[Aging Bucket], “90+ Days” )

To summarize balances by customer and aging bucket:

=SUMIFS( Invoices[Amount], Invoices[Customer],$A2, Invoices[Aging Bucket],B$1 )

Although SUMIFS offers flexibility, Pivot Tables are usually faster to build, easier to maintain, and better suited for interactive aging analysis.

For that reason, Pivot Tables should be the primary reporting method for most aging reports, while SUMIFS can be useful for dashboard KPIs and custom summary layouts.

Automate Aging Reports with Power Query

If you create aging reports every week or month, formulas may not be enough.

Power Query can automate the data preparation process.

A typical workflow looks like this:

1. Export invoice data from your accounting system or ERP.
2. Import the file into Power Query.
3. Clean column names and data types.
4. Convert due dates to date format.
5. Add a Days Outstanding column.
6. Add an Aging Bucket column.
7. Load the cleaned data back to Excel.
8. Build Pivot Tables and dashboards from the output.
9. Refresh the report when new data arrives.

This is useful when your invoice file changes regularly but the structure remains the same.

Instead of copying formulas manually each time, you refresh the query.

Power Query is especially valuable for:

• Large invoice lists
• Monthly reporting
• ERP exports
• Repetitive finance reports
• Standardized dashboards

You can still use Excel formulas after loading the Power Query output, but the heavy cleaning work happens before the data reaches the report.

Common Aging Formula Mistakes

Dates Stored as Text

If Excel does not recognize your due dates as real dates, aging formulas may return incorrect results.

Signs that dates may be stored as text:

• Dates align left by default
• Changing the format does not change the display
• Formulas return errors
• Sorting dates produces strange results

You can test a date with:

=ISNUMBER([@[Due Date]])

If the result is TRUE, Excel recognizes the value as a date.

If the result is FALSE, the date may be stored as text.

You can sometimes convert it with:

=DATEVALUE([@[Due Date]])

But be careful with regional date formats, especially if your source data uses different date conventions.

Why DATEVALUE can fail: DATEVALUE interprets text based on your system’s regional date settings. If your data uses DD/MM/YYYY (common outside the US) but your Excel is set to MM/DD/YYYY, DATEVALUE may misread the date entirely, or return a #VALUE! error for dates where the day exceeds 12 (since there is no 13th month).

If DATEVALUE returns an error or misreads your dates, try one of these instead:

Option 1: Text to Columns
Select the column, go to Data > Text to Columns, choose Delimited (or Fixed Width), and on the final step select Date and pick the format that matches your source data (DMY, MDY, or YMD). Excel converts the text to real dates without misinterpreting the order.

Option 2: Manual parsing with DATE
If your due dates are stored as text in a known DD/MM/YYYY format, you can rebuild them explicitly:

=DATE(VALUE(RIGHT([@[Due Date]],4)),VALUE(MID([@[Due Date]],4,2)),VALUE(LEFT([@[Due Date]],2)))

This avoids relying on DATEVALUE’s locale guessing entirely, since you are telling Excel exactly which characters represent the day, month, and year.

Using Invoice Date Instead of Due Date

This is one of the biggest conceptual mistakes.

If you calculate aging from invoice date, you are measuring invoice age.

If you calculate aging from due date, you are measuring overdue days.

For accounts receivable collections, due date is usually more useful.

Before building the report, decide which one you need.

Negative Aging Values

Future invoices can produce negative results.

This can break bucket logic or create confusing reports.

Use:

=MAX(0,TODAY()-[@[Due Date]])

or:

=MAX(0,$B$1-[@[Due Date]])

if you use a fixed report date.

Blank Due Dates

Blank due dates can produce misleading aging values.

Use:

=IF([@[Due Date]]=””,””,MAX(0,$B$1-[@[Due Date]]))

This keeps the report clean and prevents blank dates from being treated as very old invoices.

Hardcoded Bucket Logic

Nested formulas are fine for quick reports.

But if your aging logic is used every month, hardcoded bucket definitions can become risky.

For reusable reports, use a bucket table and INDEX MATCH.

This makes the logic visible, editable, and easier to audit.

Wrong Bucket Boundaries

Be precise with boundaries.

For example:

• 0–30
• 31–60
• 61–90
• 91+

Do not accidentally create overlapping buckets like:

• 0–30
• 30–60
• 60–90

This creates confusion because 30 and 60 appear in two categories.

Also avoid gaps such as:

• 0–29
• 31–60

In this case, day 30 is missing.

Clean bucket definitions prevent reporting errors.

Frequently Asked Questions

What is the aging formula in Excel?

The basic aging formula in Excel is:

=TODAY()-DueDate

This calculates the number of days between today and the due date.

A safer version is:

=MAX(0,TODAY()-DueDate)

This prevents negative values for invoices that are not due yet.

How do I create 30, 60, and 90 day aging buckets in Excel?

Use this formula if Days Outstanding is in G4:

=IF(G4<=30,”Current”,

IF(G4<=60,”31-60 Days”,

IF(G4<=90,”61-90 Days”,

“90+ Days”)))

For a cleaner modern version, use IFS:

=IFS(

G4<=30,”Current”,

G4<=60,”31-60 Days”,

G4<=90,”61-90 Days”,

G4>90,”90+ Days”

)

What is the best aging formula in Excel?

For quick reports, IFS is usually the easiest option.

For reusable professional reports, INDEX MATCH with a bucket table is often better because the bucket definitions can be changed without editing the formula. It also works across more Excel versions than XLOOKUP.

Example:

=IF(G4=””,””,INDEX($M$4:$M$7,MATCH(G4,$L$4:$L$7,1)))

with Excel Table References:

=IF([@[Days Outstanding]]=””,””,INDEX(BucketTable[Aging Bucket],MATCH([@[Days Outstanding]],BucketTable[Min Days],1)))

For most modern workflows, a combination of a bucket table and INDEX MATCH offers the best balance between flexibility, compatibility, and maintainability.

Should I use invoice date or due date?

Use due date if your goal is to measure overdue invoices.

Use invoice date if your goal is to measure how old invoices are from the issue date.

For accounts receivable collections, due date is usually the better choice.

How do I calculate aging as of a specific date?

Place the report date in a cell, such as B1.

Then use:

=$B$1-E4

A safer version is:

=MAX(0,$B$1-E4)

This is better for month-end reports because the result does not change every day.

Can I use Pivot Tables for aging reports?

Yes. Pivot Tables are one of the easiest ways to summarize aging data.

Use:

• Rows: Customer
• Columns: Aging Bucket
• Values: Amount

This creates a customer aging summary.

Can I automate aging reports in Excel?

Yes. You can use Power Query to import invoice data, clean it, calculate days outstanding, assign aging buckets, and refresh the report when new data is available.

This is useful for recurring finance reports.

Final Thoughts

An aging formula in Excel may look simple at first.

At the most basic level, you are subtracting a due date from today’s date.

But a useful aging report goes much further.

A professional aging report should calculate days outstanding, assign accurate 30, 60, and 90 day buckets, summarize balances by customer, highlight risky invoices, and support management decisions.

For simple reports, IF or IFS may be enough.

For better long-term reporting, use a dynamic bucket table with INDEX MATCH.

For summaries, use Pivot Tables or SUMIFS.

For recurring reports, use Power Query.

When these pieces come together, Excel becomes a powerful accounts receivable reporting tool that helps you monitor overdue balances, prioritize collections, and understand cash flow risk more clearly.