Excel Spreadsheet date comparison

Closed
Posts
1
Registration date
Wednesday June 12, 2013
Status
Member
Last seen
June 12, 2013
-
Posts
7099
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
January 20, 2022
-
Hello,

I am trying to compare 2 columns of data to determine if one date is prior to or after another date. (Is date in column A on, before, or after column B?) I have 8000 lines to complete so I am looking for a possible formula (if-then function?) that can compute this quickly.

Thanks

1 reply

Posts
7099
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
January 20, 2022
490
Helo,

Which version of excel do you have ?
You should use conditional formatting.

or the following vba macro which put a red interior colour to the cell containing the oldest date (open VisualBasic Editor by using Alt+F11 keys, click on ThisWorbook and copy/past the macro) :

Sub Test()
Dim LastRowA As Long
Dim MyPlage As Range, Cel As Range

    With ThisWorkbook.Worksheets("Sheet1") 'Sheet name need to be adapted to your workbook
        LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
       
        Set MyPlage = .Range("A2:A" & LastRowA)
      
        For Each Cel In MyPlage
                If DateDiff("d", Cel.Offset(0, 1), Cel) < 0 Then
                        Cel.Interior.ColorIndex = 3
                Else
                        Cel.Offset(0, 1).Interior.ColorIndex = 3
                        Cel.Offset(0, 2) = ""
                End If
           
        Next Cel
     
        Set MyPlage = Nothing
    End With
End Sub




Regards