#DATE EXPECTED error in COLLECT

Options

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!