Excel Match two datetime - excluding seconds-CodePudding

I need to compare 2 datetimes including hh:mm (but not ss) and if same retrieve a value, below is the format I tried. Both formats are datetime. I tried using index match below but no luck and getting errors, pls guide

{=INdex(B1:B5,Match(1,A1:A5=C2),0)}


C
Compare following dates =
26-09-2022 09:16
26-09-2022 09:17
26-09-2022 09:18'

with 
A                           B
26-09-2022 09:16:35         Value1
26-09-2022 09:17:20         Value2
26-09-2022 09:18:12         VAlue3
26-09-2022 09:19:12         VAlue3
26-09-2022 09:20:12         VAlue3

CodePudding user response：

Use DateDiff:

If DateDiff("n", FirstDateValue, SecondDateValue) = 0 Then
    ' Match.
End If

CodePudding user response：

Something like this?

Sub GetValues()
    Dim rResultCell As Range
    Dim rValueCell As Range

    For Each rResultCell In Range("C1:C3").Cells
    
        For Each rValueCell In Range("B1:B5").Cells
    
            If Left(rValueCell.Offset(, -1).Value, 16) = rResultCell.Value Then
            
                rResultCell.Offset(, 1) = rValueCell.Value
                
            End If
     
        Next rValueCell
    
    Next rResultCell

End Sub

CodePudding user response：

you can use =Int(A1:A5) Time(Hour(A1:A5),Minute(A1:A5),0). The Int part takes care of the date and the Time(hour,minute,0) will remove the seconds from the search.

example of working formula:

=XLOOKUP(INT(A15) TIME(HOUR(A15),MINUTE(A15),0),INT(A$1:A$5) TIME(HOUR(A$1:A$5),MINUTE(A$1:A$5),0),C$1:C$5,"not found",0,1)

CodePudding user response：

Having the Date in columns A:A and C:C, as seen in your picture, you can use the next formula to match the cells on the same row:

=IF(ROUND(C2-(A2 - SECOND(A2) * 0.0000115741),6)=0,"Match","")

0.0000115741 is the value of a second, in the way Excel keeps time...