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

Malin picture Malin · Oct 29, 2015 · Viewed 25.9k times · Source

If feels like this should be really easy but I dont get it to work without retrieving the value of the cell again.

To start with, I have 2 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 NOT?!

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 the if statement in a clean and easy way?

Answer

Excel Hero picture Excel Hero · Oct 29, 2015

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

Check for zero:

If agreedDate = 0 Or completedDate = 0 Then

But a safer path would be to change the variables to type Variant and then do this test:

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