Google Sheet - Dealing with unusual date format with MAXIFS function-CodePudding

I'm trying to filter data based on email address matching and date matching. My issue is the dates come in an unusual format (e.g: 2023-01-10 01:21:45 UTC).

I've been using a helper column instead to make the formula lighter. However, for automation purposes it would be much easier to have the true date format integrated to the formula.

Below is the formula I'm currently using in A2. My goal is to remove column D from formula and use only column C instead. I was trying to replace 'Sample Data for LOOKUP'!D:D by =ARRAYFORMULA(DATEVALUE(left(C2:C14,10))) but it does not work.

=filter('Sample Data for LOOKUP'!E:E,'Sample Data for LOOKUP'!D:D=MAXIFS('Sample Data for LOOKUP'!D:D,'Sample Data for LOOKUP'!B:B,"<>",ARRAYFORMULA(REGEXREPLACE('Sample Data for LOOKUP'!A:A,"(. @)",)),REGEXREPLACE(B2,"(. @)",)),(REGEXREPLACE(B2,"(. @)",)=ARRAYFORMULA(REGEXREPLACE('Sample Data for LOOKUP'!A:A,"(. @)",))),('Sample Data for LOOKUP'!B:B<>""))

Here is a sample file to make it easier to understand my issue:

https://docs.google.com/spreadsheets/d/18R-4m682SeHNBgnzJCM1l0Fef13D5vYG3gmeWDveSYQ/edit#gid=1722273432

CodePudding user response：

I'm trying in cell A2 to cut column D in 'Sample Data for LOOKUP' tab. I would like to use column C instead with the unusual date format.

To get the latest Status for each email address domain listed in column B2:B, use vlookup() and sort(), like this:

=arrayformula( 
  iferror( 
    vlookup( 
      regexextract(B2:B, "@(. )"), 
      sort( 
        { regexextract(Lookup!A2:A, "@(. )"), Lookup!E2:E }, 
        Lookup!C2:C, 
        false 
      ), 
      2, 
      false 
    ) 
  ) 
)

See your sample spreadsheet.