Converting Text to Dates... Fast!

[kirby1024]

Hello All,

I'm running an Excel 2003 file, where I put some data in from a csv file (which has >6000 rows currently). Specifically, the data I'm using is a date field within that data, but copying it from the csv file gives it to me as a text field, which is useless for all the formulas I've set up.

So! I figured a simple and easy way to do it would be to create a Macro in Worksheet_change for the data sheet, that would go through every entry in the date column, changing the text there to date format until it hit a blank cell. This worked fine (if a touch slow as it used a loop), so I left it as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A3").Select
Do While IsEmpty(ActiveCell.Value) = False
ActiveCell.Value = CDate(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Then I put the formulas in. Now, what was acceptably slow is basically taking forever, as every iteration of the loop invokes a formula recalculation!

So, I have two questions:

1) Is it possible to disable formula recalculation during a script? I've tried using

Application.EnableEvents = False

to turn it off, but of course since I've put it in an Application event... Also, doesn't seem to affect formula recalculation anyway.

2) Is there a way that I can do what I want to do (changing a text-version of a date into a date value) without the loop? The formula recalculation wouldn't be such a big issue if it didn't happen every time. For comparison, the format that the CSV file spews at me is:

Oct 23, 2008 12:35:53 PM

I've tried just changing the cell variable normally, but for some reason it won't budge, hence this workaround.

Help?!

[Demosthine]

Good Evening.

There are two things I can think of that should immediately help you out.

First, in answer to turning Calculation off, the answer is yes.

The .EnableEvents method will prevent recursive Events from firing like the Worksheet_Change Event. But the Formulas are still calculated. To fix this, you want to set:
[vba]
Application.Calculation = xlCalculationManual

' Process your code.

Application.Calculation = xlCalculationAutomatic
[/vba]

The second method is that instead of changing each cell's Value, you should change it's Format.

[vba]
Dim rngDate As Range

Set rngDate = Range("A:A")

rngDate.NumberFormat = "mm/dd/yyyy HH:mm"
[/vba]

Hope this speeds things up.
Scott

[kirby1024]

The second method is that instead of changing each cell's Value, you should change it's Format.

[vba]
Dim rngDate As Range

Set rngDate = Range("A:A")

rngDate.NumberFormat = "mm/dd/yyyy HH:mm"
[/vba]

Hope this speeds things up.
Scott

See, it seems like that this should work, since the format looks like a date. But for some reason, simply changing the number format doesn't do anything at all in this case, which is why I went with the value changing version in the first place (I suspect that's because while it looks like a date, it's not actually in a date format that the cell formatting will pick up). Very odd, I will admit. Thankyou for the Calculation stopper though!

[Demosthine]

Can you post the Workbook? Or a small sample of it. Then we can find out what the "raw" data looks like.

Scott

[kirby1024]

Hopefully I've attached the first 100 rows of the source file (normally I'd copy/paste this source data into the actual spreadsheet) Column A is where the data I wish to convert is. If it helps, I'm using Excel 2003 to edit and play with the file. Did you want me to attach an abridged version of my working workbook as well?

[NanaHawk]

Hi kirby,
I'm new to this stuff but have you tried

Dim rngDate As Range

Set rngDate = Range("A3:A6102")

For Each cVal In rngDate
cVal.Value = CDate(cVal)
Next
rngDate.NumberFormat = "mm/dd/yyyy HH:mm"

It seems to work quickly for me but maybe my expectations are lower than yours.

[dalea]

I may be missing something but the data in column "A" in the file you uploaded IS ALREADY an excel date serial number. You can select the column heading, then format, then cells and change the format to any acceptable date format. Format the column as a number and you can see the serial number. Maybe you just have to change your file name to an *.xls file when you save it. You are operating on what is displayed as though that is what is in the cell. If I'm missing something - NEVERMIND!

[Paul_Hossler]

My thoughts:

1. Don't use Worksheet_Change, use a regular Module and put your code in that.

From your code, it looks like you don't want this to fire every time you make ANY changes to that worksheet (manually or via a macro).

2. The CSV is a text file (change the ext to .TXT and look) but XL will be nice and convert it for you as best it can. It was able to convert Col A to 'real' serial number dates.

3. If you just re-open the CSV each time you'll need a real XL workbook to keep your macros since XL doesn't store macros in CSV files, or do some cutting and pasting

4. No need to loop, do the whole column at once

This is in a Module, not a Worksheet

[vba]
Option Explicit
Sub Macro1()
ActiveSheet.Columns("A:A").NumberFormat = "mm/dd/yy"
End Sub
Sub Macro2()
ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeConstants, xlNumbers).NumberFormat = "mm/dd/yy"
End Sub
[/vba]

There is also the non-VBA way to just select Col A and Format, Number, Date, since they are XL serial dates



Paul