date data type is not a date??

Following is code I am running.  I intend to compare two dates.  One date 
(orderrg.value) is from a spreadsheet and is defined as a type date.  The 
other date is the system date. However when I check to see if the 
orderrg.value is a date via ISDATE, it fails.  Following the code is the 
prinout from the immediate window.  It looks to me like it should be able to 
recognize it as a date, but yet it doesn't.  Does anyone have any ideas?


Private Sub Workbook_Open()

Dim wscommission As Worksheet
Dim nindex As Integer
Dim lastorder As Integer
Dim sheetname As String
Dim orderws As Worksheet
Dim orderrg As range

Set wscommission = ThisWorkbook.Worksheets("commission")
Application.Cursor = xlNorthwestArrow

'DETERMINE IF ANY ORDERS ARE READY TO APPLY TO COMMISSION

Debug.Print Date

lastorder = ThisWorkbook.Worksheets.count - 8  'sets ending range to 'the 
last possible order
Debug.Print lastorder
For nindex = 3 To lastorder  'process 1st order thru the last order
    sheetname = ThisWorkbook.Worksheets(nindex).Name  'set up the sheetname
    Debug.Print sheetname
    'Sheets(sheetname).Select
    Debug.Print ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex
   'PROCESS THRU THE ORDERS WHOSE COLOR IS LIGHT GREEN (CARRIER HAS BEEN 
ASSIGNED)
    If ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex = 35 Then
          'IF THE ORDER'S DELV DATE IS LESS THAN THE CURRENT DATE; PROCESS IT
          Set orderws = ThisWorkbook.Worksheets(sheetname)
          Set orderrg = orderws.range("E24")
          Debug.Print orderrg.Value
         'Dorderdate = CDate(orderrg.Value)
         ' orderdate = orderrg.Value
          
          If IsDate(Date) Then
            If IsDate(orderrg.Value) Then
             'include code to compare the dates once I get valid dates
            Else
            GoTo usererror
            End If
          Else
            GoTo usererror
            End If
     Else
        Exit Sub
     End If
 Next
 
usererror:
Debug.Print "date invalid"

End Sub


HERE IS THE DISPLAY FROM THE IMMEDIATE WINDOW
4/22/2010 
 3 
D100001
 35 
2/29/2010
date invalid        
-1
Utf
4/22/2010 9:06:11 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
816 Views

Similar Articles

[PageSpeed] 52

2010 isn't a leap year, so 2/29/2010 isn't a date.



Phyllis wrote:
> 
> Following is code I am running.  I intend to compare two dates.  One date
> (orderrg.value) is from a spreadsheet and is defined as a type date.  The
> other date is the system date. However when I check to see if the
> orderrg.value is a date via ISDATE, it fails.  Following the code is the
> prinout from the immediate window.  It looks to me like it should be able to
> recognize it as a date, but yet it doesn't.  Does anyone have any ideas?
> 
> Private Sub Workbook_Open()
> 
> Dim wscommission As Worksheet
> Dim nindex As Integer
> Dim lastorder As Integer
> Dim sheetname As String
> Dim orderws As Worksheet
> Dim orderrg As range
> 
> Set wscommission = ThisWorkbook.Worksheets("commission")
> Application.Cursor = xlNorthwestArrow
> 
> 'DETERMINE IF ANY ORDERS ARE READY TO APPLY TO COMMISSION
> 
> Debug.Print Date
> 
> lastorder = ThisWorkbook.Worksheets.count - 8  'sets ending range to 'the
> last possible order
> Debug.Print lastorder
> For nindex = 3 To lastorder  'process 1st order thru the last order
>     sheetname = ThisWorkbook.Worksheets(nindex).Name  'set up the sheetname
>     Debug.Print sheetname
>     'Sheets(sheetname).Select
>     Debug.Print ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex
>    'PROCESS THRU THE ORDERS WHOSE COLOR IS LIGHT GREEN (CARRIER HAS BEEN
> ASSIGNED)
>     If ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex = 35 Then
>           'IF THE ORDER'S DELV DATE IS LESS THAN THE CURRENT DATE; PROCESS IT
>           Set orderws = ThisWorkbook.Worksheets(sheetname)
>           Set orderrg = orderws.range("E24")
>           Debug.Print orderrg.Value
>          'Dorderdate = CDate(orderrg.Value)
>          ' orderdate = orderrg.Value
> 
>           If IsDate(Date) Then
>             If IsDate(orderrg.Value) Then
>              'include code to compare the dates once I get valid dates
>             Else
>             GoTo usererror
>             End If
>           Else
>             GoTo usererror
>             End If
>      Else
>         Exit Sub
>      End If
>  Next
> 
> usererror:
> Debug.Print "date invalid"
> 
> End Sub
> 
> HERE IS THE DISPLAY FROM THE IMMEDIATE WINDOW
> 4/22/2010
>  3
> D100001
>  35
> 2/29/2010
> date invalid

-- 

Dave Peterson
-1
Dave
4/22/2010 9:59:04 PM
Thank you Dave,
boy do I feel stupid.  I was so busy concentrating on what was wrong with 
the date format that it never occured to me. 

"Dave Peterson" wrote:

> 2010 isn't a leap year, so 2/29/2010 isn't a date.
> 
> 
> 
> Phyllis wrote:
> > 
> > Following is code I am running.  I intend to compare two dates.  One date
> > (orderrg.value) is from a spreadsheet and is defined as a type date.  The
> > other date is the system date. However when I check to see if the
> > orderrg.value is a date via ISDATE, it fails.  Following the code is the
> > prinout from the immediate window.  It looks to me like it should be able to
> > recognize it as a date, but yet it doesn't.  Does anyone have any ideas?
> > 
> > Private Sub Workbook_Open()
> > 
> > Dim wscommission As Worksheet
> > Dim nindex As Integer
> > Dim lastorder As Integer
> > Dim sheetname As String
> > Dim orderws As Worksheet
> > Dim orderrg As range
> > 
> > Set wscommission = ThisWorkbook.Worksheets("commission")
> > Application.Cursor = xlNorthwestArrow
> > 
> > 'DETERMINE IF ANY ORDERS ARE READY TO APPLY TO COMMISSION
> > 
> > Debug.Print Date
> > 
> > lastorder = ThisWorkbook.Worksheets.count - 8  'sets ending range to 'the
> > last possible order
> > Debug.Print lastorder
> > For nindex = 3 To lastorder  'process 1st order thru the last order
> >     sheetname = ThisWorkbook.Worksheets(nindex).Name  'set up the sheetname
> >     Debug.Print sheetname
> >     'Sheets(sheetname).Select
> >     Debug.Print ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex
> >    'PROCESS THRU THE ORDERS WHOSE COLOR IS LIGHT GREEN (CARRIER HAS BEEN
> > ASSIGNED)
> >     If ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex = 35 Then
> >           'IF THE ORDER'S DELV DATE IS LESS THAN THE CURRENT DATE; PROCESS IT
> >           Set orderws = ThisWorkbook.Worksheets(sheetname)
> >           Set orderrg = orderws.range("E24")
> >           Debug.Print orderrg.Value
> >          'Dorderdate = CDate(orderrg.Value)
> >          ' orderdate = orderrg.Value
> > 
> >           If IsDate(Date) Then
> >             If IsDate(orderrg.Value) Then
> >              'include code to compare the dates once I get valid dates
> >             Else
> >             GoTo usererror
> >             End If
> >           Else
> >             GoTo usererror
> >             End If
> >      Else
> >         Exit Sub
> >      End If
> >  Next
> > 
> > usererror:
> > Debug.Print "date invalid"
> > 
> > End Sub
> > 
> > HERE IS THE DISPLAY FROM THE IMMEDIATE WINDOW
> > 4/22/2010
> >  3
> > D100001
> >  35
> > 2/29/2010
> > date invalid
> 
> -- 
> 
> Dave Peterson
> .
> 
1
Utf
4/23/2010 5:00:01 PM
Sometimes, those kinds of errors just need an extra pair of eyes.

Phyllis wrote:
> 
> Thank you Dave,
> boy do I feel stupid.  I was so busy concentrating on what was wrong with
> the date format that it never occured to me.
> 
> "Dave Peterson" wrote:
> 
> > 2010 isn't a leap year, so 2/29/2010 isn't a date.
> >
> >
> >
> > Phyllis wrote:
> > >
> > > Following is code I am running.  I intend to compare two dates.  One date
> > > (orderrg.value) is from a spreadsheet and is defined as a type date.  The
> > > other date is the system date. However when I check to see if the
> > > orderrg.value is a date via ISDATE, it fails.  Following the code is the
> > > prinout from the immediate window.  It looks to me like it should be able to
> > > recognize it as a date, but yet it doesn't.  Does anyone have any ideas?
> > >
> > > Private Sub Workbook_Open()
> > >
> > > Dim wscommission As Worksheet
> > > Dim nindex As Integer
> > > Dim lastorder As Integer
> > > Dim sheetname As String
> > > Dim orderws As Worksheet
> > > Dim orderrg As range
> > >
> > > Set wscommission = ThisWorkbook.Worksheets("commission")
> > > Application.Cursor = xlNorthwestArrow
> > >
> > > 'DETERMINE IF ANY ORDERS ARE READY TO APPLY TO COMMISSION
> > >
> > > Debug.Print Date
> > >
> > > lastorder = ThisWorkbook.Worksheets.count - 8  'sets ending range to 'the
> > > last possible order
> > > Debug.Print lastorder
> > > For nindex = 3 To lastorder  'process 1st order thru the last order
> > >     sheetname = ThisWorkbook.Worksheets(nindex).Name  'set up the sheetname
> > >     Debug.Print sheetname
> > >     'Sheets(sheetname).Select
> > >     Debug.Print ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex
> > >    'PROCESS THRU THE ORDERS WHOSE COLOR IS LIGHT GREEN (CARRIER HAS BEEN
> > > ASSIGNED)
> > >     If ActiveWorkbook.Sheets(sheetname).Tab.ColorIndex = 35 Then
> > >           'IF THE ORDER'S DELV DATE IS LESS THAN THE CURRENT DATE; PROCESS IT
> > >           Set orderws = ThisWorkbook.Worksheets(sheetname)
> > >           Set orderrg = orderws.range("E24")
> > >           Debug.Print orderrg.Value
> > >          'Dorderdate = CDate(orderrg.Value)
> > >          ' orderdate = orderrg.Value
> > >
> > >           If IsDate(Date) Then
> > >             If IsDate(orderrg.Value) Then
> > >              'include code to compare the dates once I get valid dates
> > >             Else
> > >             GoTo usererror
> > >             End If
> > >           Else
> > >             GoTo usererror
> > >             End If
> > >      Else
> > >         Exit Sub
> > >      End If
> > >  Next
> > >
> > > usererror:
> > > Debug.Print "date invalid"
> > >
> > > End Sub
> > >
> > > HERE IS THE DISPLAY FROM THE IMMEDIATE WINDOW
> > > 4/22/2010
> > >  3
> > > D100001
> > >  35
> > > 2/29/2010
> > > date invalid
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
4/23/2010 6:38:36 PM
Reply:

Similar Artilces:

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

C# Adding Days to a Date
Hello, I have 2 objects: objContract.activeon and objContract.expireson. I am trying to add 364 days to objContract.activeon and assign it to the value of objContractexpireson. ---------------------------------------------------------- // Contract Start Date DateTime dt = DateTime.Now; objContract.activeon = new CrmDateTime(); int iFound = 0; string sTemp = ""; if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays Date { objContract.activeon = objInvoice.CFDinvoicedate; } else // ...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Adding support for mfc / atl types in a Win32 project
MSDN says this about adding support for classes shared between atl & mfc (I'm just after points and rects btw)... just add the header ( atltypes.h ). I'm getting linker errors for these classes though. Is there a lib I need to add? Any screwy build options need changing? It's in VC 2003. In the project wizard I note that atl and mfc support aren't available when creating an empty Win32 application. Ta. On 26 May 2006 07:42:09 -0700, Simon L wrote: > MSDN says this about adding support for classes shared between atl & > mfc (I'm just after points and rect...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

how do I change date format in the header in Excel XP ?
I need to chage my date format, in the header to Day; Month DD, YYYY ie. Saturday; May 23, 2005. Woudl you please help me out ? Thank you. Regards, Hesam Shakourian Check this out. http://support.microsoft.com/?kbid=213742 but change the format to "dddd, MMMM dd, yyyy" "Hesam" <Hesam@discussions.microsoft.com> wrote in message news:93495F75-4196-4208-9C0D-E800BCAE3A89@microsoft.com... > I need to chage my date format, in the header to Day; Month DD, YYYY > ie. Saturday; May 23, 2005. > > Woudl you please help me out ? > > Thank you. > &g...

Fulfillment type orders
We have orders at the initial status (just entered) and the options to allow delete documents and void documents are enabled on the Sales Order Type. However, we can't get a void or delete button unless we move the fulfillment order through the confirm process to an "Invoice" to get a Delete? What's up with this? Very annoying. Jana, I know it is subtle and wish there was a better way to display it, but the Void option only applies to Invoice type IDs and not Fulfillment Order type IDs ("Void Invoice Documents" vs. "Delete Documents"). Since both t...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

Option trades with past expiry dates not showing up in Portfolio M
Hello: Money 2006 Portfolio Manager does not show closed option trades that have expiry dates in the past, even when the "show closed positions" is checked. The transactions are still there in the investment accounts, just not visible in portfolio manager. I just called microsoft support and have notified them of the issue and hope that this issue is fixed as an update. If not, it pretty much makes the portfolio manager (and Microsoft Money 2006) useless for option trading investors. "MumbaiBabu" <MumbaiBabu@discussions.microsoft.com> wrote in message news:1...

How do you sort a date range by month?
We are trying to find out how many birthdays fall with in a given month using excel. You could use a helper column, with something like = A2. Format this (Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals, "At each change in: Month" and "Use function: Count." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" <Brewisc13@discussions.microsoft.com> wrote in message news:44E02AAC-8216-43F5-846F-E981E978E44B@microsoft.com... > We are trying to find out how many birthdays fall with in...

vb date and ssn textbox formats
Ok, so maybe I am crazy...but I have checked "Visual Basic 6 how t program", and "Visual basic 5 teach yourself in 24 hrs" for and exampl or how to format a text box on a user form to accept and display date and Social Security Numbers in the specified format. Thanks Al -- Message posted from http://www.ExcelForum.com Date TextBox1.Text = Format(Date,"dd mmm yyyy") SSNs TextBox1.Text = Format(mySSN, "000-00-0000") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mai...

Source data, in Chart Menu, is grey and not accessible.
I set up a pivot table and designed a chart, saved and closed out of the workbook. Upon returning to work on this project, when selecting the chart menu to work with my source data, the source data is grey and not accessible. What might be causing this and can I correct it? It's a pivot chart. As soon as it's created, it's linked permanently to the pivot table, and you cannot change the source data. you cannot add more data that's not in the pivot table to the chart, nor can you remove data that is in the pivot table from the chart. - Jon ------- Jon Peltier, Microsoft...

Two calendar dates in one cell..??
I have a Microsoft Publisher calendar document that was created in 2005 that shows the dates Monday - Sunday. When I print it, it will not print out any dates that fall after the fifth week (eg January 2006 it prints up to January 29th but not the 30th or 31st). These are in an "overflow" area. But when I open the calendar part in Word to edit the calendar, it appears normally and that sixth week shows up just fine. I need that sixth week to print OR I need to do the split date feature (I would prefer the split date). How can I set up the numbers in one of those cells so i...

One table with two date fields returning records in a date range
I have a table that has several columns, two of which are date fields. A start_date and comp_date for start and completion date. I need to run a query that will return all the records between two dates selected by a user. Not hard, piece of cake. The problem I am having is that my query isn't returning all the records that overlap a given date range. For example: I select a date range: June 1 to September 1. My query will return 10 records that start and stop between June 1 and September 1. This is good and correct, but.... ...I have several records that start before June ...

OWA Has date as March
Hi, I just mirgrated from 2000 to 2003 and on one user the the date Via OWA shows March - its ok thru the outlook client itself. It causing all his old meetings to popup. Any ideas? Lee ...

Missing Data Options
I am trying to use Tools - Options - Chart. I want to use the "not Plotted" option. When I click on it my chart does not change. This happens after I've selected the chart and then trying to make these changes. What am I doing wrong? I want to create a chart that ignores missing data, and gives me a trend line based on the data I have. I've had my nose in my Excel "bible" but can't find the answer. I greatly appreciate any tips. Jim -- jimsmith888 ------------------------------------------------------------------------ jimsmith888's Profile: http...

Extracting data and returing to colums.
I am about half way there, but I need some more help. These are web inquires that parsed out in the same colum and repeat th same way all down the page. I want to be able to use the fields as guidelines to pull the dat adjacent to it and sent to the new colum. In a way it is transposin all the data, but I need to pull all the first names of these peopl and have them align in the first colum. Same for Last name, business etc. So if the information that I have come across like this in th spreadsheet, I just want the customer infomation to move to th appropriate colums like the third example...

how do I convert Mozilla thunderbird data to outlook 2010?
Converted from an old Outlook to Mozilla Thunderbird years ago. Like new Outlook and would like to switch back. Can't seem to see a conversion utility -- Mishka I suspect you export from thunderbird to a format Outlook can import, eg csv (assuming pop mail) "Mishka" <Mishka@discussions.microsoft.com> wrote in message news:509C3250-167B-4FFF-9E07-5E5A8C625AA8@microsoft.com... > Converted from an old Outlook to Mozilla Thunderbird years ago. Like new > Outlook and would like to switch back. Can't seem to see a conversion > utility > -- >...

date Format #5
I am from South Africa. My bank uses the date format yyyy-MM-dd and I have set the money format to the same one (and all the others available) but cannot import a Quicken file. Keep getting the message: You file couldn't be imported because its date format doesn't match your Money file's format. Any more setting one can change because I have tried all the date formats. bertus In microsoft.public.money, Bertus wrote: >I am from South Africa. My bank uses the date format yyyy-MM-dd and I have >set the money format to the same one (and all the others available) but &g...

Graphing daily data
I am trying to graph daily data for a month in Excel 2007 that groups like days (Saturdays, Sundays, etc) in the same series, while still showing individual values and dates. Any ideas on how to accomplish this? Use your worksheet to pull the relevant data together into a series. Do the data manipulation in your worksheet, then the chart plots the series which you have generated in the worksheet. -- David Biddulph "Fozzie" <Fozzie@discussions.microsoft.com> wrote in message news:EB004134-AD88-434C-9DAD-BFE4CB2FD058@microsoft.com... >I am trying to graph daily data f...