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

  • Paul Newcome
    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
    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
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for context?

  • Meesh
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!