Forum Discussion
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 missing
- If 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
=IF(COUNTIFS($A$2:$A$18,A2,$D$2:$D$18,"`")>0,"Missing sent date",IF(COUNTIFS($A$2:$A$18,A2,$E$2:$E$18,"`")>0,"Record still open",MAX(IF($A$2:$A$18=A2,$E$2:$E$18))))
This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
2 Replies
=IF(COUNTIFS($A$2:$A$18,A2,$D$2:$D$18,"`")>0,"Missing sent date",IF(COUNTIFS($A$2:$A$18,A2,$E$2:$E$18,"`")>0,"Record still open",MAX(IF($A$2:$A$18=A2,$E$2:$E$18))))
This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
thank you so much! The formula works perfectly, and I appreciate the reminder about "ctrl+shift+enter".
