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.
Answers
-
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))
-
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?
-
Are you able to provide some screenshots for context?
-
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.
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!