Invoice Aging

Options
✭✭✭✭

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???

• ✭✭✭✭✭✭
Options

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. 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.

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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!