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

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!