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!

Find more posts tagged with

Formulas

Accepted answers

All comments

Heather Duff

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

Automations 1

@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.

Heather Duff

@Automations 1 Try moving the parenthesis:

=INDEX(COLLECT({Evergreen Totals}, {Admission Type}, "Start of Business", {Date}, TODAY(-1)), 1)

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.