Index/Collect with a date being one of the criteria
Hi,
I'm trying to write an index/collect formula and one of the criteria is that the date is yesterday. I'm having trouble. Any suggestions?
=INDEX(COLLECT({Evergreen Totals}, {Admission Type}, "Start of Business", {Date}, TODAY() - 1, 1))
Thank you!
Answers
-
Hi @Automations 1 ,
I think you're close! Try this: =INDEX(COLLECT({Evergreen Totals}, {Admission Type}, "Start of Business", {Date}, TODAY(-1), 1))
The parenthesis after TODAY are where you enter the adjustment to how many days before (negative) or after (positive) today.
Hope this helps! Let me know if it works!
Best,
Heather
-
@Heather Duff Thanks, but I'm still getting #Incorrect Argument
I ended up creating a helper column with an if statement that checks if the date is yesterdays date, so i got the results i wanted. But I am curious why the Today(-1) function is not working.
-
@Automations 1 Try moving the parenthesis:
=INDEX(COLLECT({Evergreen Totals}, {Admission Type}, "Start of Business", {Date}, TODAY(-1)), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!