How to check if a date cell is empty in Excel?

Question

If it seems like this should be very simple, but I can't get it to work without returning the cell value again.

For starters, I have two date cells:

Dim agreedDate As Date Dim completedDate As Date 

THIS WORKS .. (but looks messy)

 agreedDate = Worksheets("Data").Cells(Counter, 7).Value completedDate = Worksheets("Data").Cells(Counter, 9).Value If (IsEmpty(Worksheets("Data").Cells(Counter, 7).Value) = True) Or (IsEmpty(Worksheets("Data").Cells(Counter, 9).Value) = True) Then [.. do stuff] End If 

THIS DOES NOT WORK - WHY ?!

 agreedDate = Worksheets("Data").Cells(Counter, 7).Value completedDate = Worksheets("Data").Cells(Counter, 9).Value If (IsEmpty(agreedDate) = True) Or IsEmpty(completedDate) = True) Then [.. do stuff] End If 

Is there a way to write an if statement in a clean and simple way?

Answer 1

Since only variables of type Variant can be empty, you need a different test for Date types.

Check zero:

 If agreedDate = 0 Or completedDate = 0 Then 

But a safer way would be to change the variables of type Variant, and then run this test:

 If IsDate(agreedDate) = False Or IsDate(completedDate) = False Then 

Answer 2

The IsEmpty function determines if the variable has been initialized . If the cell is really empty, it is considered uninitialized from the point of view of IsEmpty . However, declaring a variable in VBA gives a default value. In this case, date type variables are essentially 0 or 30-Dec-1899 00:00:00 , as shown in the following short snippet.

 Sub date_var_test() Dim dt As Date Debug.Print Format(dt, "dd-mmm-yyyy hh:mm:ss") End Sub 

If you want to "remove" your code, you can also enable the true logical return of the IsEmpty function to solve the logical condition, and not for comparison with True .

 If IsEmpty(Worksheets("Data").Cells(Counter, 7)) Or IsEmpty(Worksheets("Data").Cells(Counter, 9)) Then [.. do stuff] End If 

Given that False is (for all purposes and tasks) zero, then this will work for your date type variables.

 If Not (agreedDate or completedDate) Then [.. do stuff] End If 

Answer 3

As Excel Hero noted, a date variable cannot be empty. In fact, the date variable is a number, so you should be able to do something like this. Also note that the code below uses the value "Value2".

 Sub test() Dim d As Date d = Range("A1").Value2 If d = 0 Then MsgBox "ok" Else MsgBox "not ok" End If End Sub