Previous Topic Next Topic

Excel Spreadsheet date comparison

Closed
JohnnyB3 Posts 1 Registration date Wednesday June 12, 2013 Status Member Last seen June 12, 2013 - Jun 12, 2013 at 03:13 PM
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 - Jun 12, 2013 at 06:26 PM
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
Related:

1 response

Answer 1 / 1
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Jun 12, 2013 at 06:26 PM
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
0

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses