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

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