excel if statement with a date

I have a date in cell A1 (10/19/2005) that I am trying to write an If statement for and it keeps returning it as False when I put in the statement builder A1="10/19/2005" I only have this problem with dates, why?

I think you can't use an If statement if it's not a number value???

Does VBA support the IsDate Function?

If so, you can use that.

Post your code attempt ... that will help us to determine exactly what you are trying to do.

Quote:

---

Originally Posted by vonoventwin

when I put in the statement builder A1="10/19/2005" I only have this problem with dates, why?

---

didn't write code I used statement builder

I don't know about "Statement Builder", but dates work just fine in VBA code. You just have to consider that the Date/Time data type is a unique animal. It is essentially a floating point number with special handling/formatting/conversion.

Code:

---

'A1    10/19/05
'A2    10/22/05
'A3    10/30/05
'A4    10/31/05
'A5    11/2/05
Option Explicit
Sub Macro1()
If Cells(3, "A").Value > Cells(2, "A").Value Then
    MsgBox Cells(2, "A").Value
Else
    MsgBox Cells(3, "A").Value
End If

If Cells(4, "A").Value > Now() Then
    MsgBox Cells(4, "A").Value
Else
    MsgBox Now()
End If
End Sub

---

A date is not a string. As I said, I don't know about "Statement Builder", but try A1 = #10/19/2005#. SQL notation for dates is like this.

The date is recognized as 38644 in General format, so I wrote my code as A1=38644 and it sees it as true even though the format is still as a date. Weird.. But it works!

=IF(A1=DATEVALUE("12/10/2005"),"True","False")

DATEVALUE converts the string (if possible) to a Date/Time data type. I hope this helps you. I still don't know what "Statement Builder" is or how you get into it, but it is probably just a wizard for the Formula Editor field.

Going off your work I did this:

VB Code:

Sub Macro1()
If Cells(2, "J").Value = "10/19/2005" Then
    Cells(2, "L").Value = Cells(2, "I").Value * 0.3
    MsgBox "30%"
Else
    Cells(2, "L").Value = Cells(2, "I").Value
    MsgBox "Not 30%"
End If
 
End Sub

What I want to do now is make this code work for the whole sheet. Basically:

VB Code:

If Column("J").Value = "10/19/2005" Then
Column("L").Value = Column("I").Value * 0.3
    
Else
    Column("L").Value = Column("I").Value
    
End If