Convert (automatically) 1 column in spreadsheet

[JiTS]

Hi,

Every week I get a spreadsheet from a database. In this spreadsheet is a column 'DATE', see attachment. This column is filled with (many) dates but they are not correctly typed like DD-MM-YYYY.

For example: 20051210 should be 10-12-2005 (DD-MM-YYYY).

Is there a way to convert this column or cells by using a macro or something else?!?

Thanks!

BTW I cannot change the input from the database. My input will also be the same as 20051210.

 

[Rod D]

Here is some code to get you going...
Good luck.

Rod

Some assumptions:
1) Your list of dates are in column A and the first one is in row 2. If not, change the Range("XX").Activate to that cell.
2) There are no blank cells in the list. If there are, the macro will only convert up to the first blank cell.
3) The dates are always 8 digits long i.e. 1st Jan 2006 is "20060101", not "200611".


Sub ChangeDate()
'
' ChangeDate Macro
'
Range("A2").Activate
' Select from A2 to the first blank row (as if you had pressed [Shift]+[End], [Down]) and then loop through them.
For Each c In Range(Selection, Selection.End(xlDown)).Cells
' For each cell, re-arrange the numbers - last 2 digits, middle 2 and first 4, putting in a "-" between them
c.Value = Right(c.Value, 2) & "-" & Mid(c.Value, 5, 2) & "-" & Left(c.Value, 4)
Next

End Sub

 

[shades]

I would probably use another column (maybe to the left of the entire data import set so that it doesn't interfer with DB updates). Suppose your import is in cell C2, then in B2 put the following formula (note, it will not be a date per se, but text).

=RIGHT(C2,2)&"-"&MID(C2,5,2)&"-"&LEFT(C2,4)

Then copy down the column as far as needed.
________
Starcraft ii replays