Convert false DATE column to real DATE column

[Roy_Excel_Island_Apps]

Hi colleagues,

I have a DATE column transfered from an Access database, but Excel recognises it as a text (that's why it's aligned on the left). When you enter a cell in the date column and press Enter, the date will align on the right and will be recognised as a date and I will be able to use it in formulas. I have written a small procedure to convert each cell in the range to a date in the right format because I don't want to enter every cell in the column. But this procedure takes a long time in a rng of 30.000 records... Does someone have a solution to convert a large range of dates to real dates? I have already tried TextToColumns method as well... That's working for numbers, but not for dates. Other ideas?

Sub convertDate(rng As Range)


Dim aDate As Date
Dim cell As Range

For Each cell In rng

aDate = Format(CDate(cell.Value), "dd/mm/yyyy")
cell.Value = aDate

Next

End Sub

 

[Roy_Excel_Island_Apps]

Select range of dates at question and give below a try:

Text to column > Delimited > Next > Next > Date (DMY) > Finish

I need to do it with VBA. Manualy it works, but not with VBA.

 

[Fluff]

Ok, use

Array(1, 4)

 

[Rick Rothstein]

Can you open this link?
https://drive.google.com/file/d/1YVzhdB_F3Dd_gtAvtBYONcxeUwAHWZ1O/view?usp=sharing

Yes. Does this macro work for you (it does for me using XL2010 on Win8.1)...

[table="width: 500"]
[tr]
	[td]Sub ConvertDate()
  Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, False, FieldInfo:=Array(1, 4)
End Sub[/td]
[/tr]
[/table]

 

[Excel Planet]

Given data provided, below should work:

Selection.TextToColumns Destination:=Selection.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True

 

[jkpieterse]

How does the data get in Excel precisely?

 

[Roy_Excel_Island_Apps]

Ok, use

Array(1, 4)

This works! Thanks!

 

[Roy_Excel_Island_Apps]

That's working! Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[jkpieterse]

Precisely how does the data go from access to Excel?