Compare dates to fill a cell using VBA Excel

Question

I'm trying to compare two dates with the format DD/MM/YYYY HH:MM AM/PM, the idea is that if one date is greater than the other it puts in a field the word "Before" or "After" if it's lower.

I've tried doing this but failed miserably.

For col = 0 To objSheet.UsedRange.Rows.Count
    objSheet.Range("A2").Formula = "=IF(Value < B&(col+2), Before,After)"
Next

Line objSheet.Range("A2").Formula = "=IF(Value < B&(col+2), Before,After)" seems to not throw any error but all I get in my excel file is #NAME?

Value is a string that contains the value to which the other string is going to be compared to. Example

' Value = 5/4/2016 8:00:00 PM
' Value in B column: 5/5/2016 12:00:00 PM

then the if should output = BEFORE but if

'Value in B column: 5/5/2016 2:00:00 AM

then the output should be AFTER

I've seen some diff_date function but it doesn't seem to be integrated because it doesn't show up as an option.

It's there a way to achieve this? What am I doing wrong?

I would use a different variable than Value as it is a reserved word in vba. So lets use vlue then you formula would be: "=IF(""" & Vlue & """ < B" & col + 2 & ", Before,After)" — Scott Craner

cxw cxw · Accepted Answer · 2016-06-16T21:06:22

Edited This works for me in Excel 2013, but I've had to make some assumptions about what you're doing. The below code fills in column A depending on whether the date in Value is before or after the value in column B.

Option Explicit
Option Base 0

Public Sub PopulateDates()
    Dim dateString As String
    Dim row As Long
    dateString = "5/4/2016 8:00:00 PM"
    Dim dateRepresentation As String
    dateRepresentation = "VALUE(""" & dateString & """)"

    Dim newFormula As String
    For row = 1 To ActiveSheet.UsedRange.Rows.Count
        newFormula = "=IF(" & dateRepresentation & " < B" & CStr(row) & ", ""Before"",""After"")"
        ActiveSheet.Cells(row, 1).Formula = newFormula
    Next row
End Sub

Lots going on here.

dateString instead of Value per Scott Craner's note, and because Value is so generic you might forget what it means.
dateRepresentation is a way to represent a date as a literal in a worksheet formula. It is, e.g., VALUE("5/4/2016 8:00 PM").
newFormula is on its own line so you can check it in the debugger before you make the assignment. :)
- the row (you had col+2) is part of your VBA, so it is not part of your formula. CStr(row) makes a string (e.g., 42), and then & pastes that string after B and before ,.
ActiveSheet.Cells instead of ActiveSheet.Range because the latter didn't work for me. You would use objSheet instead of ActiveSheet

The result of all this is to put, e.g., in cell A1:

=IF(VALUE("5/4/2016 8:00:00 PM") < B1, "Before","After")

Compare dates to fill a cell using VBA Excel

1 Answers