Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MONTH function where cell is blank | Excel Discussion (Misc queries) | |||
Testing for Month in a cell using VBA | Excel Programming | |||
Force date cell to show [blank] if end of month | Excel Discussion (Misc queries) | |||
When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) | |||
testing for blank | Excel Programming |