#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

Find more posts tagged with

Formulas

References

Accepted answers

Gia Thinh

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

All comments

Gia Thinh

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

Carlos Castro

Thank you Gia Thinh!

Works like a charm

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.