We help IT Professionals succeed at work.

How Can I Convert Dates that are Text to Actual Dates with a Macro?

Rex
Rex asked
on
I have some date data that I export from SAP to Excel. I need to run a macro to clean up column names etc... anyway, so I'd like to convert the dates from text to actual dates at the same time.

I tried the Formatting technique. That did not work. Also...there are twenty thousand or so rows in each spreadsheet.

The dates look like dates, e.g., 8/18/2011, but when I import them into Access, they import as text.

Thanks.
Comment
Watch Question

Should be as simple as Datevalue(Your Date).  As long as they conform to standard date formats.

HTH
Cal
For converting a range of dates, try this, replacing the column A with your column.
Dim cell as range

for each cell in activesheet.range("A1", activesheet.range("A65535").end(xlup))
     Cell=datevalue(cell)
next cell

RexQuality Leader

Author

Commented:
my programming skills are extremely week. That seems like I would have to loop through all rows in the code, or is that a formula for entry in another column?
RexQuality Leader

Author

Commented:
I got a data type mismatch error.  Error Message What the Debugger shows after the error
That means that not all of the "Dates" are really date formats, or it could be that it's choking on a blank.  Can you copy your dates to a seperate sheet, and upload?  I can then build the code and exec ute.  If it's blanks, it should be as easy as putting an IF in to deal with them.  If it's inconsistant date formats, that might take a little more work to resolve.
Just as a note, you can accomplish this using a formulas as well, but you mentioned code, so that's what I gave you.
RexQuality Leader

Author

Commented:
Will do. Yes. Code strongly preferred. It will take me a bit.
RexQuality Leader

Author

Commented:
these are the two date columns as they are exported from SAP into Excel. I need both the daily and the month dates.

Those will be the correct column headings. They will be imported into access.

Thanks

Rex
EE-Cut-Down-Test-of-Date-Convers.xls
Damn...it was such a silly issue.  I started the loop at row 1...that's you headings, so it wasn't able to convert you heading into a datevalue.  Try the code below.  I've adjusted the code to work on Columns C & D, like your example above.



Dim cell As Range
Dim ws As Worksheet

Set ws = ActiveSheet

For Each cell In ws.Range("C2", ws.Range("D" & ws.Rows.Count).End(xlUp))

    cell = DateValue(cell)
Next cell

Open in new window

RexQuality Leader

Author

Commented:
That worked fantasticly! Thank you very much.
RexQuality Leader

Author

Commented:
Awesome. Thanks!

Rex