Date Format error. date or text.

[Combat Womble]

Hello

I recieved a load of files and use macros to sort clean etc before add the data to other files. These files are then used for data analysis. The date can be an important part of this analysis. I have descovered an error in which the date isn't a date and when the macro sorts a sheet by date the result is incorrect.

So, the error occurs when the input date is a cell that looks like 12-12-2015 (from now on I am going to call it a hyphenated cell), when the input cells look like 12/12/2015 (from now on I am going to call it a slash cell) everything works fine.

Before the macro starts, when I click on the hyphenated cell, excel tells me its a date (home ribbon, number). After the macro has finished excel still tells me its a date, but it has not been ordered by date (unlike the others). Also if i expand the column, then the hyphenated cells are left justified and the slash cells are right justified.

I have tried the following:
1. replace the hyphens with slashes

ActiveCell.value = Replace(ActiveCell.Value, "-", "/")

2. changing the cell format

Selection.NumberFormat = "dd/mm/yy"
'and'
Selection.NumberFormat = "m/d/yyyy"
'and'
ActiveCell.NumberFormat = "dd/mm/yy;@"

3. doing 1 and 2

Selection = Format(Replace(ActiveCell.Value, "-", "/"), "dd/mm/yyyy")

4 remove contents of cell, change cell format add date

If InStr(ActiveCell.Value, "-") > 0 Then
    arrayA = Split(ActiveCell, "-")
    ActiveCell.ClearContents
    ActiveCell.NumberFormat = "dd/mm/yy;@"
    ActiveCell.Value = arrayA(0) + "/" + arrayA(1) + "/" + arrayA(2)
End If

Any pointers in the right direction would be amazing and appreciated

edit:

i forgot to mention that I tried selecting the column and changing the format to date. I did this by recording a macro and copying its code.

Combat Womble

 

[mart37]

ActiveCell.Value = CDate(Replace(ActiveCell.Value, "-", "/"))
or
ActiveCell.Value = DateValue(ActiveCell.Value)
or
ActiveCell.Value = ActiveCell.Value * 1

 

[Combat Womble]

mart37
thank you very much
Combat Womble