CODEWITHSUNDEEP
vbaexceldate
Splendonia
Splendonia

Reputation: 1369

Compare dates to fill a cell using VBA Excel

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?

Upvotes: 1

Views: 452

Answers (1)

cxw
cxw

Reputation: 17041

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")

Upvotes: 1

Related Questions