Excel VB - Testing Month within a blank cell

[Dave]

I have two cells in a workbook that are dates. The cell Start_Date has a
valid date in it. The second cell Test_Date is either blank or has a valid
date in it. The IsDate test on both cells works fine, however, on a blank
cell the Month function extracts "12". Anyone know why? I had to put the
month function within a test for IsDate to make it work. Seems like Month
function on a blank cell should return something other than a 12.

Dave

Test code:

If IsDate(Range("Start_Date")) Then 'Cell has 11/01/09 in it.
MsgBox ("start date a date") 'Displays
Else
MsgBox ("start date not a date")
End If

If IsDate(Range("Test_Date")) Then 'Cell is blank
MsgBox ("test date a date")
Else
MsgBox ("test date not a date") 'Displays
End If

MsgBox (Month(Range("Start_Date"))) 'Displays 11
MsgBox (Month(Range("Test_Date"))) ' Displays 12

If Month(Range("Start_Date")) = Month(Range("Test_Date")) Then
MsgBox ("Months equal")
Else
MsgBox ("months not equal") 'Displays
End If

 

[Robert Flanagan]

Dave, why not test to see if the value of the cell is zero? Or
Isempty(cellref). That will tell you the cell is empty

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Dave Peterson]

Try:

msgbox format(0,"mmmm dd, yyyy")

And you'll see why month(0) returns 12.

Then try:

msgbox format(-12345,"mmmm dd, yyyy")

And you'll see that VBA handles negative dates/numbers differently than excel.

 

[Mike H]

Dave,

It's all to do with the way Excel stores date. It stores them as a number
with 1 being 1/1/1900 so zero (or a blank cell) evaluates as 31/12/1899 so
the month is 12. In keeping with the season -5 will evaluate as Christmas day
1899!!

Include a test for a blank cell

Mike

 

[Mike Fogleman]

If you do not give Month a date to test (blank), then it assumes today's
date. If you try it today it should result in 1 (January).

Mike F

 

[Mike Fogleman]

OOPS! I was wrong.. Mike H is correct.

Mike F

If you do not give Month a date to test (blank), then it assumes today's
date. If you try it today it should result in 1 (January).

Mike F

 

[Dave]

Thanks Robert, Dave, and Mike,

I now understand clearly what is going on and why it is happening with the
date.

Looks like an IsDate or IsBlank test works fine to correct the problem.

Thanks guys,
Dave

 

[Dave Peterson]

In VBA, you can use:

If Range("Start_Date").value = "" Then
(empty or maybe a formula that evaluates to "")

or to test for real emptyness:
If Isempty(Range("Start_Date").value) Then
(no formula, no value)