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

Question

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?

You have declared the variables as type Date. Only Variant type variables can be Empty. — Excel Hero, Oct 29 '15 at 18:10
Why use IsEmpty() = True instead of just IsEmpty() for the conditions? It's already evaluating to a Boolean. — Janine White, Dec 09 '21 at 20:07

Excel Hero · Accepted Answer · 2015-10-29T18:23:08.773

11

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

edited Oct 29 '15 at 18:23

answered Oct 29 '15 at 18:17

Excel Hero

14,253
4
33
40

Great answer, this was exactly what I was looking for! – Malin Oct 29 '15 at 18:39

score 4 · Answer 2 · 2015-10-29T18:30:24.653

4

The IsEmpty function determines indicated whether a variable has been initialized. If a cell is truly blank then it is considered uninitialized from the IsEmpty standpoint. However, declaring a variable in VBA gives it a default value. In this case the date type variables are essentially 0 or 30-Dec-1899 00:00:00 as demonstrated by 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 'tidy up' your code, you might also wish to allow the true boolean return of the IsEmpty function to resolve the boolean condition rather than comparing it to 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 intents and purposes) zero then this will work for your date type variables.

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

edited Oct 29 '15 at 18:30

answered Oct 29 '15 at 18:23

2

I think the best route is to go with variants and use IsDate(). What if the cell has text in it... – Excel Hero Oct 29 '15 at 18:25
Dates have always been a little funny. They're numbers but actually more than than numbers. While predominantly accurate, the [IsDate Function](https://msdn.microsoft.com/en-us/library/office/gg278584.aspx) is still a best-guess. I prefer to work with the [Range.Value2 property](https://msdn.microsoft.com/en-us/library/office/ff193553.aspx) and treat dates as numbers until they have to be put back into the worksheet. – Oct 29 '15 at 18:29
1

@Excel Hero I agree with Excel Hero. Variant should be used as the intended return of both Value and Value2. If the cell contains text then passing it's value to a date variable produces a run-time error. So the best form would be to use variant types and IsDate(). If date values are needed, they can be assigned once the date value has been verified. Although I also agree that treating dates as numbers is easiest if you can guarantee that they are in fact dates to begin with. – u8it Oct 29 '15 at 18:34
@P57 & xlHero I guess the use of a variant would depend upon how volatile your data was. Passing over an error in data (string found where date expected) is not always the best solution. Are you going to halt the sub before proceeding? Are you going to skip that record and somehow report the error? If I found a string where I expected a date, I would like to know immediately because my first fear would be a bad import where ambiguous DMY vs. MDY were converted incorrectly and unambiguous DMY vs MDY were converted to strings making the whole operation useless. – Oct 29 '15 at 18:40
The main point is that the intended return value of both Value and Value2 is variant. Any abnormal situation should be handled from this basis, not by producing (or requiring error handling) of a run-time error. Subsequently, the IsDate function seems to be the most convenient way of validating the variant cell value as a date. It also makes the code inherently self descriptive. All other scenarios can still be handled as needed. – u8it Oct 29 '15 at 18:56

score 1 · Answer 3 · answered Oct 29 '15 at 18:18

As Excel Hero pointed out, a date variable cannot be empty. In fact, a date variable is a number, so you should be able to do something like this. Also, notice that the code below uses "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

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

3 Answers3

Linked