Excel VB - Testing Month within a blank cell
 

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

Excel VB - Testing Month within a blank cell
 

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" wrote in message
...
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

Excel VB - Testing Month within a blank cell
 

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.



Dave wrote:

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



--

Dave Peterson

Excel VB - Testing Month within a blank cell
 

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

"Dave" wrote:

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




.

Excel VB - Testing Month within a blank cell
 

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" wrote in message
...
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

Excel VB - Testing Month within a blank cell
 

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

Mike F
"Mike Fogleman" wrote in message
...
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" wrote in message
...
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

Excel VB - Testing Month within a blank cell
 

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" wrote in message
...
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

Excel VB - Testing Month within a blank cell
 

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)






Dave wrote:

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" wrote in message
...
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



--

Dave Peterson