Invoice Aging
![Micah Turner](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
-
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
- 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
- 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.
- 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")))))
- 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
- 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
- Enter Data:
- Start entering invoice data including the invoice date and mark the "Paid in Full" checkbox as appropriate.
- 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.
- Create Columns:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!