Next Expense Date Formula
I need to create a formula that will find the next expense date based off 3 other column categories. The columns and their categories are as follows: Due Date (single date when expense is due), Program ( Men, Women), and Budget Impact (Income, Expense). For this formula I need to find and return the next expense date from today's date that also correlates with the Men category from column Program and the Expense category from the Budget Impact column.
I have tried the following, however, it returns Incorrect Argument
=INDEX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], >TODAY(), Program:Program, "Men", [Budget Impact]:[Budget Impact], "Expense"))
Thank you in advance for your assistance.
Best Answer
-
Hi @FouFou
If you're looking to get the first date for your criteria, you just need to add a 1 to the formula. The new formula would be
=INDEX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], >TODAY(), Program:Program, "Men", [Budget Impact]:[Budget Impact], "Expense"), 1)
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers
-
Hi @FouFou
If you're looking to get the first date for your criteria, you just need to add a 1 to the formula. The new formula would be
=INDEX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], >TODAY(), Program:Program, "Men", [Budget Impact]:[Budget Impact], "Expense"), 1)
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Hello Aravind,
Thank you for that! It worked.
Can I ask another related question to this formula if I want to find if the expense is approved or not. I was thinking something like this:
=IF(AND(MIN(COLLECT([Due Date]:[Due Date] < TODAY()))) Approved:Approved = 0, "Awaiting Expense Approval", "Nothing overdue")
But this ^ doesn't seem to work.
Thank you in advance,
FouFou 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!