#DATE EXPECTED error in COLLECT
Greetings
I have been trying to figure this out for a couple days now.
Let's say I'm trying to get expiration dates for fruits, which is 1 week after they are bought. However, if I have two apples (initial apple and follow-up apple), I'm only interested in the expiration date for the initial apple.
After a lot of attempts, I ended up with an assistant table that holds all the expiration dates for initial fruits, which are already linked to follow up ones via IDs. So this is my formula:
=IF([Type]@row = "Follow-up", COLLECT({ExpirationDates_Reference}, {FruitID_Reference}, InitialFruitID@row), DATE(YEAR(PurchaseDate@row), MONTH(PurchaseDate@row), DAY(PurchaseDate@row)+7))
And here is an example of my tables:
As you can see the second part of the IF works great, but I can't get the second part to work. I have made sure both columns are Date type. I have tried with INDEX(MATCH)) and VLOOKUP with similar results (#INVALID DATA TYPE). VALUE() doesn't seem to change anything either.
This is probably very simple but I am stumped. Any help is greatly appreciated
Best Answer
-
Hi Carlos Castro,
COLLECT function must be used within another function and can not stand alone. Just wrap it with JOIN() function as below. Hope it works for you.
=IF([Type]@row = "Follow-up", JOIN(COLLECT({ExpirationDates_Reference}, {FruitID_Reference}, InitialFruitID@row)), DATE(YEAR(PurchaseDate@row), MONTH(PurchaseDate@row), DAY(PurchaseDate@row)+7))
Gia Thinh Technology - Smartsheet Solution Partner.
Answers
-
Hi Carlos Castro,
COLLECT function must be used within another function and can not stand alone. Just wrap it with JOIN() function as below. Hope it works for you.
=IF([Type]@row = "Follow-up", JOIN(COLLECT({ExpirationDates_Reference}, {FruitID_Reference}, InitialFruitID@row)), DATE(YEAR(PurchaseDate@row), MONTH(PurchaseDate@row), DAY(PurchaseDate@row)+7))
Gia Thinh Technology - Smartsheet Solution Partner.
-
Thank you Gia Thinh!
Works like a charm
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!