Multiple records, same user, evaluate from and to dates to define a status
I was hoping for help with a formula…
Across all records with a given UserID…First determine if there are any records with a missing Sent Date (I have replaced all blank cells with the “`” (backtick/ Grave accent) character)If so, the “status” value for all records for that user should be “Missing Sent Date”If there are no missing Sent Dates, then check all of the Received Dates for that user to see if any are missingIf one or more records has a missing received Date, then the “status” value for all records for that user should be “Record Still Open”If there are no missing Sent or Received Dates across all records for the the user…Then check display the Max Received date (this will become the data that the record is closed)
Here’s a sample of the data
I assume I would use a combination of “if”, “filter”, and “Max” formulas, but I’m unsure where to go from there.
Thank you.
Adam
I was hoping for help with a formula…Across all records with a given UserID…First determine if there are any records with a missing Sent Date (I have replaced all blank cells with the “`” (backtick/ Grave accent) character)If so, the “status” value for all records for that user should be “Missing Sent Date”If there are no missing Sent Dates, then check all of the Received Dates for that user to see if any are missingIf one or more records has a missing received Date, then the “status” value for all records for that user should be “Record Still Open”If there are no missing Sent or Received Dates across all records for the the user…Then check display the Max Received date (this will become the data that the record is closed)Here’s a sample of the data I assume I would use a combination of “if”, “filter”, and “Max” formulas, but I’m unsure where to go from there. Thank you.Adam Read More