Invoice Aging

Micah Turner

I need to create a sheet that will add the days from when an invoice is issued and show an indicator for if it is 1-30 days, 30-60 days, 60-90 days, 90-120, or over. And an option to mark when it is paid in full. Any suggestions???

Lucas Rayala

@Micah Turner

This is pretty simple—I asked ChatGPT to create some instructions for you and this looks pretty solid. Let me know if you get stuck and I can help you out.

Step-by-Step Instructions

1. Set Up Your Sheet

1. Create Columns:
   • Invoice Date: Date column to enter the date the invoice was issued.
   • Days Since Issued: Text/Number column to calculate the number of days since the invoice was issued.
   • Status: Text/Number column to display the age range (1-30, 31-60, 61-90, 91-120, Over 120 days).
   • Indicator: Symbols column to visually indicate the age range.
   • Paid in Full: Checkbox column to mark if the invoice is paid.

2. Add Formulas

1. Days Since Issued:
   • =IF([Paid in Full]@row, "", TODAY() - [Invoice Date]@row)
   • This formula calculates the number of days since the invoice was issued if it’s not marked as paid.
2. Status:
   • =IF([Paid in Full]@row, "Paid", IF([Days Since Issued]@row <= 30, "1-30 days", IF([Days Since Issued]@row <= 60, "31-60 days", IF([Days Since Issued]@row <= 90, "61-90 days", IF([Days Since Issued]@row <= 120, "91-120 days", "Over 120 days")))))
3. Indicator:
   • =IF([Paid in Full]@row, "✔️", IF([Days Since Issued]@row <= 30, "🟢", IF([Days Since Issued]@row <= 60, "🟡", IF([Days Since Issued]@row <= 90, "🟠", IF([Days Since Issued]@row <= 120, "🔴", "⚫")))))

3. Format the Sheet

1. Conditional Formatting (Optional):
   • To enhance visibility, you can add conditional formatting rules to change the background color of the rows based on the "Status" column.

4. Test and Use

1. Enter Data:
   • Start entering invoice data including the invoice date and mark the "Paid in Full" checkbox as appropriate.
2. Verify Calculations:
   • Ensure that the "Days Since Issued," "Status," and "Indicator" columns are correctly reflecting the age of the invoice and the payment status.

Example Sheet Layout

| Invoice Date | Days Since Issued | Status | Indicator | Paid in Full |
|--------------|-------------------|--------------|-----------|--------------|
| 2023-01-01 | 120 | 91-120 days | 🔴 | [ ] |
| 2023-03-01 | 60 | 31-60 days | 🟡 | [ ] |
| 2023-04-15 | 30 | 1-30 days | 🟢 | [ ] |
| 2023-05-01 | | Paid | ✔️ | [x] |

By following these steps, you can effectively track invoice age, visually represent the age ranges, and mark when invoices are paid in full. This setup provides a clear and organized way to manage and monitor invoice statuses.