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 |
date data type is not a date??
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 |
date data type is not a date??
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 . |
date data type is not a date??
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 |
All times are GMT +1. The time now is 07:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com