Excel VBA: Optimal use of DateValue in VBA (or other method to compare dates)

1
votes

I've written a VBA macro in Access that's creating a new excel file basing on a few different ones. I'm unable to change the way the source files are formatting the data, and I want to write a simple IF statement (if date X is past date Y, delete entire row). The problem is that the dates have different format. The way I'm able to do it is create a new column:

=IF(DATEVALUE(A2)>DATEVALUE(H2),TRUE,FALSE)

And then go from that and at the end delete the column. That doesn't feel right though and I believe there's a better solution. I've been trying to do something like 

 If ifDelete = vbYes And .Cells(Lrow, 19).Value Like "*timing*" And DateValue(.Cells(Lrow, 1).Value) > DateValue(.Cells(Lrow, 8).Value) Then
      .Cells(Lrow, 19).EntireRow.Delete
 End If

But it obviously doesn't work. I also tried working with formatting the date columns prior to running that IF statement, but still, no luck. If that helps, dates are formatted in a way MM/DD/YYYY and M/D/YYYY. For some reason going with .NumberFormat = "MM/DD/YYYY" on the 2nd or both fields doesn't work too.

Kind regards, heaton124.

1
vbaexceldateif-statement
Obviously, you are talking about STRINGS CONTAINING DATE-LIKE VALUES and not about real date values. The easiest and RIGHT way to do what you are looking for is to make sure you compare DATES AGAINST DATES by converting within your vb those strings to internal date values. – FDavidov
That's correct and I'm aware of it. When I'm using code like that on a test file: Range("A1:J1").NumberFormat = "mm/dd/yyyy" And then compare two dates it doesn't help either. The values in those cells doesn't even change whether I'm putting "mm/dd/yyyy" or "mm-dd-yyyy" or anything else. Not sure what's the problem here. – heaton124
can you share a screen-shot of your "so-called" dates data ? so we can see how they are stored, and in what format ? – Shai Rado
There it is. link I also included the "general" type in the top right corner. When I use the macro to change the NumberFormat it does indeed change to "custom", but doesn't really affect the understanding of the data by my other macros. btw. that data is from January if that's important at all. – heaton124
Did you try using the VBA function CDate()? – FDavidov

1 Answers

3
votes

Following p.phidot's suggestion, I put my comment as an answer so it can be marked and hence reached by others.

So, the answer is:

Try using the VBA function CDate().

Cheers.