Next Expense Date Formula

I need to create a formula that will find the next expense date based off 3 other column categories. The columns and their categories are as follows: Due Date (single date when expense is due), Program ( Men, Women), and Budget Impact (Income, Expense). For this formula I need to find and return the next expense date from today's date that also correlates with the Men category from column Program and the Expense category from the Budget Impact column.

I have tried the following, however, it returns Incorrect Argument

=INDEX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], >TODAY(), Program:Program, "Men", [Budget Impact]:[Budget Impact], "Expense"))

Thank you in advance for your assistance.

Find more posts tagged with

Formulas

dates

Accepted answers

AravindGP

Hi @FouFou

If you're looking to get the first date for your criteria, you just need to add a 1 to the formula. The new formula would be

=INDEX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], >TODAY(), Program:Program, "Men", [Budget Impact]:[Budget Impact], "Expense"), 1)

All comments

AravindGP

Hi @FouFou

If you're looking to get the first date for your criteria, you just need to add a 1 to the formula. The new formula would be

=INDEX(COLLECT([Due Date]:[Due Date], [Due Date]:[Due Date], >TODAY(), Program:Program, "Men", [Budget Impact]:[Budget Impact], "Expense"), 1)

FouFou

Hello Aravind,

Thank you for that! It worked.

Can I ask another related question to this formula if I want to find if the expense is approved or not. I was thinking something like this:

=IF(AND(MIN(COLLECT([Due Date]:[Due Date] < TODAY()))) Approved:Approved = 0, "Awaiting Expense Approval", "Nothing overdue")

But this ^ doesn't seem to work.

Thank you in advance,

FouFou 🙂

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.