Accounts Receivable Days and Aging Buckets in Excel

Accounts Receivable Days Walkthrough

1. Create a Month Name Column from the Date

• Add a new column to extract the month name from your date field.
  
• Use the TEXT function to convert the date into a full month name:
  
  • =TEXT([DateCell],”mmmm”)
    
• Fill the formula down for the entire dataset.
  

2. Calculate Days to Collect for Each Invoice

• Add a “Days to Collect” column.
  
• If you have both an Invoice Date and a Payment Date, calculate days by subtracting:
  
  • Payment Date – Invoice Date
    
• Handle unpaid invoices so they do not distort your averages:
  
  • Wrap the subtraction in IFERROR so unpaid rows return a blank instead of 0.
    
  • The goal is to avoid “0” being included in averages (which would lower the average incorrectly).
    
• Fill the formula down so every invoice has either a days-to-collect value or a blank.
  

3. Build a Month List for Reporting

• Type Month as the header in a summary area.
  
• Under it, type January and drag down until December appears.
  

4. Calculate Average Days to Collect by Month

• In the summary table, calculate the average days-to-collect for each month using AVERAGEIFS.
  
• Structure:
  
  • Average range: your Days-to-Collect column
    
  • Criteria range: your Month Name column
    
  • Criteria: the month name in your summary list (January, February, etc.)
    
• Fill the formula down through December.
  
• Adjust decimals if desired.
  

5. Create the Line Chart for Accounts Receivable Days by Month

• Highlight the Month list and the Average Days result.
  
• Insert a Line Chart (Insert → Line) or use Recommended Charts.
  
• Apply your preferred styling (the video uses a dark theme).
  
• If you want to remove decimals:
  
  • Click the axis values → Format Axis → Number → set decimal places to 0.
    
• If you want the values displayed on the chart:
  
  • Turn on Data Labels and format them the same way.
    

6. Create Aging Buckets Using IFS

• Add a new column that assigns each invoice to an aging bucket based on Days to Collect.
  
• Use IFS (cleaner than nested IF statements) to output bucket labels such as:
  
  • 0 to 15
    
  • 16 to 30
    
  • 31 to 45
    
  • 46 to 60
    
  • 60+
    
  • Outstanding
    
• Important detail from the video:
  
  • Outstanding invoices should be labeled as Outstanding, not 60+.
    
  • If you used IFERROR(…,””), Excel may treat “blank” as a non-truly blank value.
    
  • Instead of checking ISBLANK(D3), check if the Days-to-Collect cell equals empty text:
    
    • If Days-to-Collect equals “”, label it Outstanding.
      
• Fill the bucket formula down through the dataset.
  

7. Sum Invoice Amount by Bucket

• Create a small bucket summary table with your bucket labels listed down a column.
  
• Use SUMIFS to sum Invoice Amount by each bucket:
  
  • Sum range: Invoice Amount column
    
  • Criteria range: Bucket column
    
  • Criteria: bucket label in your summary table (example: 0 to 15)
    
• Format the result as currency.
  

8. Count Invoices by Bucket

• Use COUNTIFS to count how many invoices fall into each bucket:
  
  • Criteria range: Bucket column
    
  • Criteria: bucket label
    
• Copy the formula down for each bucket row.
  

9. Calculate Percent of Total

• Compute “Percent of Total” using:
  
  • =BucketCount / SUM(AllBucketCounts)
    
• Lock the denominator with F4 so it does not move when dragging the formula down.
  
• Format as a percentage.
  

10. Create the Aging Buckets Chart with Two Axes

• Highlight the Bucket labels and the Invoice Amount totals.
  
• Hold Ctrl and also select the Percent of Total column.
  
• Insert a Combo chart from Recommended Charts:
  
  • Bars for Invoice Amount (primary axis)
    
  • Line for Percent of Total (secondary axis)
    
• Apply the dark theme styling and title it something like:
  
  • AR by Days