Hoping someone can help with a problem I have in Excel.
I regularly extract large volumes of data from an AS400
system and much of the process I use is automated. The
program I use extracts the data from the system and then
automatically imports the data into Excel and enters the
relevant data breaks and assigns data types to the data.
Unfortunately the date field is always imported as text
and this causes me problems when sorting using that
column. I have tried formatting cells but the system just
won't have it. The only way I can get the dates to work is
to go to each cell, press F2 then enter. After this the
system immediately realises that the text is actually a
date and converts for me. However, I often need to do this
for up to 40,000 lines at a time and it is just not
practical. I have tried a basic macro (but I am not really
any good at it) but I can get it to run through the F2,
enter process for me - instead it pastes the data from the
cells I recorded the macro in - as I said only a basic
user when it comes to macros!

Does anyone know how I can set up a macro to do this for
me? Alternatively any tips on how I can persuade Excel to
recognise the data as dates without the need to run
through this process? Unfortunately I cannot at present
adjust the original macro that imports the data.

Any help greatly appreciated.

You should be able to adapt this procedure to do what you want:

Sub Tester()
Dim sDate As String
Dim dDate As Date
Columns(1).NumberFormat = "yyyy-mm-dd"
sDate = "2003-05-23"
dDate = sDate
Cells(1, 1) = dDate
End Sub


"Matthew Spinks" wrote in message
...
Hoping someone can help with a problem I have in Excel.
I regularly extract large volumes of data from an AS400
system and much of the process I use is automated. The
program I use extracts the data from the system and then
automatically imports the data into Excel and enters the
relevant data breaks and assigns data types to the data.
Unfortunately the date field is always imported as text
and this causes me problems when sorting using that
column. I have tried formatting cells but the system just
won't have it. The only way I can get the dates to work is
to go to each cell, press F2 then enter. After this the
system immediately realises that the text is actually a
date and converts for me. However, I often need to do this
for up to 40,000 lines at a time and it is just not
practical. I have tried a basic macro (but I am not really
any good at it) but I can get it to run through the F2,
enter process for me - instead it pastes the data from the
cells I recorded the macro in - as I said only a basic
user when it comes to macros!

Does anyone know how I can set up a macro to do this for
me? Alternatively any tips on how I can persuade Excel to
recognise the data as dates without the need to run
through this process? Unfortunately I cannot at present
adjust the original macro that imports the data.

Any help greatly appreciated.

Two ways;

Non-macro:

Select your column. Choose Data/Text to Columns.Click Next, Next. In
the Date dropdown, choose the appropriate format. Click Finish.


A macro that does the above:

Public Sub TextToDates()
Columns("A:A").TextToColumns _
Destination:=Range("A1"), _
FieldInfo:=Array(1, 3)
End Sub

In article ,
"Matthew Spinks" wrote:

Hoping someone can help with a problem I have in Excel.
I regularly extract large volumes of data from an AS400
system and much of the process I use is automated. The
program I use extracts the data from the system and then
automatically imports the data into Excel and enters the
relevant data breaks and assigns data types to the data.
Unfortunately the date field is always imported as text
and this causes me problems when sorting using that
column. I have tried formatting cells but the system just
won't have it. The only way I can get the dates to work is
to go to each cell, press F2 then enter. After this the
system immediately realises that the text is actually a
date and converts for me. However, I often need to do this
for up to 40,000 lines at a time and it is just not
practical. I have tried a basic macro (but I am not really
any good at it) but I can get it to run through the F2,
enter process for me - instead it pastes the data from the
cells I recorded the macro in - as I said only a basic
user when it comes to macros!

Does anyone know how I can set up a macro to do this for
me? Alternatively any tips on how I can persuade Excel to
recognise the data as dates without the need to run
through this process? Unfortunately I cannot at present
adjust the original macro that imports the data.

Any help greatly appreciated.