I am needing help with a formula that includes multiple dates and criteria.

I'm needing help with a formula I can use to verify the last submitted entry "Created Date" that matches my "Identifier" and Projected End Date is not blank, I want to collect the "Projected End Date" or if the Projected End Date is blank on the last entry I want to collect the "Class End Date" for that "Identifier".

Here are the column keys I'm referencing:

My identifier column is: "Identifier"

Date to collect if the Projected End Date is not blank in the last submitted entry for that identifier: "Projected End Date"

Date to collect if Projected End Date is blank for the last submitted entry matching the identifier: "Class End Date"

The date referencing the last entry created and matches the Identifier criteria is: "Created Date"

Here is the formula I'm using that isn't working:

=IF([Projected End Date]@row = "", [Class End Date]@row, COLLECT([Projected End Date]@row, Identifier:Identifier, CONTAINS(Identifier@row, @cell), MAX([Created Date]:[Created Date]) <= TODAY()))

On another note, I want to collect that date and house it under a specific row within that identifier that matches day 1. I created a column for this date which is called: "Actual End Date".

Please let me know if you have any questions, I would greatly appreciate any help offered. Thank you.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

I would suggest using a helper column to pull the appropriate date.

=IF([Projected End Date]@row <> "", [Projected End Date]@row, [Class End Date]@row)

From there you would use this to grab the last entered:

=INDEX([Helper Column]:[Helper Column], MATCH(MAX([Created Date]:[Created Date]), [Created Date]:[Created Date], 0))

Meesh

Thank you @Paul Newcome . The only issue is it's not grabbing the date last submitted for the Identifier rows only. Its adding one date on all rows. Am I able to only grab the last submitted entry for that identifier and if it has a projected date that isn't blank then it grabs that date and if it doesn't then it grabs the class end date?

Paul Newcome

Are you able to provide some screenshots for context?

Meesh

Yes, thank you for your help. Here's a screenshot of the columns in reference. The identifier column is the VZLearn Activity Code column. It's an identifier that captures each class being trained by day. So I need the last day trained actual end date. If the projected date was before or after the Class End Date I need that date for the last day trained. If the projected end date is blank I just need the Class end date as the last date.

Meesh

@Paul Newcome were you able to take a look at this? Sorry, I didn't tag you when I responded and I don't know it if alerts you unless I do tag you.

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.