Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Colum Date Formatting & Validation
Hi all
I have read a number of informative threads on similar issues.. I have a worksheet that imports data. This data contains a few columns that are DATES - except that the information is not always in a defined format. Some data is text, others dates etc etc I have written a macro that cycles through the data and checks to see if the cell contains a DATE (IsDate function) or not. If it doesn't have a date, it attempts to see if it can recover date information from it. This worked OK until I came across a dataset that contains dates that were on the left of the cell - the function recognised them as dates, yet the analysis system gave a "runtime 13". Whatever format option i chose, it seemed to NOT include these cells / affect these cells I then tried the text-to-column function - which worked perfectly... except on O97 - i am sure it is due to the VBA attributes. Anyone know which of the following I can drop for compatibility ? Many thanks Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo :=Array(1, 4), TrailingMinusNumbers:=True |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Colum Date Formatting & Validation
Try getting rid of the ", TrailingMinusNumbers:=true" portion.
This was added in xl2002 (IIRC). Mike wrote: Hi all, I have read a number of informative threads on similar issues... I have a worksheet that imports data. This data contains a few columns that are DATES - except that the information is not always in a defined format. Some data is text, others dates etc etc. I have written a macro that cycles through the data and checks to see if the cell contains a DATE (IsDate function) or not. If it doesn't have a date, it attempts to see if it can recover date information from it. This worked OK until I came across a dataset that contains dates that were on the left of the cell - the function recognised them as dates, yet the analysis system gave a "runtime 13". Whatever format option i chose, it seemed to NOT include these cells / affect these cells. I then tried the text-to-column function - which worked perfectly... except on O97 - i am sure it is due to the VBA attributes. Anyone know which of the following I can drop for compatibility ? Many thanks. Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 4), TrailingMinusNumbers:=True -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
How do I get year to date account of an expense listed in a colum | Excel Worksheet Functions | |||
Sumif a date time colum and information | Excel Discussion (Misc queries) | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Date Validation - Must equal Sundays date | Excel Discussion (Misc queries) |