VBA macros isDate() function returning true for non-date value

[1man]

Hi Everybody,
I have a code which needs to check the element of array is date or non date ,if the value is true i need to format the value with oracle to_date function which will be then inserted into DB .But when i use the isDate function it returns true for non-date value (9999-0002). . From the array , I will get a element like 12/31/2015 .For example , if isDate(12/31/2015) is true then the condition would be to_date('12/31/2015','YYYY-MM-DD ') " but as the isDate() function is returning true for 9999-0002 , it is appending the to_date for all the non-date values.Please suggest me a vba function which will check for actual date value
My actual code look like below

Please Login or Register  to view this content.

[Norie]

9999-0002 is seen by Excel VBA as a valid date, it's the 1st February 9999.

Try adding code to check the year after you've checked for a date.

[1man]

Hi Noorie,
Thanks for your reply,
I have checked using the Year function also and it returns 9999 which may not considered as valid value for date formatting. Please suggest

[Norie]

Did you add a check to see if the year was in a suitable range?

Perhaps something like this which checks that the year is between 2000 and 2030 inclusive.

Please Login or Register  to view this content.

By the way, does 9000-2 or similar values represent anything? For example an error code.

[1man]

Thanks you very much norie..Your solution worked..
By the way to answer your question... 9999-002 is just a data feed representing a item-code and doesnt represent any error code

[Norie]

Why is it in a field/column that should have dates?

[1man]

Actually array has series of values which means that it contains the values of different cell values which includes Item_code column and also Valid_date columns.
Required is to add the to_date format for valid_date column alone.

Thanks