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

 

[Fluff]

Text to columns should work, did you select a date format of MDY?

 

[steve the fish]

Its a surprise text to columns doesnt work when reentering the cell does. Ive never seen that. Did you try it using text to columns and telling it that you are dealing with dates in that column? Text to columns-delimited-next-next-click date and DMY.

 

[jkpieterse]

Are you sure the source data is really in a date format? WHat does your query look like?

 

[Roy_Excel_Island_Apps]

Its a surprise text to columns doesnt work when reentering the cell does. Ive never seen that. Did you try it using text to columns and telling it that you are dealing with dates in that column? Text to columns-delimited-next-next-click date and DMY.

I forgot to say: The text to colums method is working only MANUALY, not with VBA:

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

 

[Fluff]

Try changing this

Array(1, 1)

to

Array(1, 3)

 

[Roy_Excel_Island_Apps]

Try changing this

Array(1, 1)

to

Array(1, 3)

That doens't help... It just copies the dates to another column with the same issue

 

[Rick Rothstein]

That doens't help... It just copies the dates to another column with the same issue

Can you show us a few samples of what your "text dates" look like?

 

[Roy_Excel_Island_Apps]

Can you show us a few samples of what your "text dates" look like?

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

 

[Excel Planet]

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

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