Excel Spreadsheet date comparisonClosed

Question

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

aquarelle · Answer

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

Excel Spreadsheet date comparison

1 response

Similar discussions

Subscribe To Our Newsletter!