Excel doesn't recognize dates

Question

I discovered a strange problem when trying to run a macro.

In my sheet I have a column that I copied from another sheet with dates like this:

|     D      | 
|15/01/2013  |
|20/01/2013  |
|01/02/2013  |

All the cells in this column are in the "Date" type with a "dd/mm/yyyy" format.

If with a macro I want to compare the year of each dates on by one it will work, for example if I do:

Year(Range("D1").Value)=Year(Range("D2").Value) it returns a True statement

But if I try to compare the dates themselves it won't work.

For example if I try to do:

Range("D1").Value <= Range("D2").Value it will return a False statement.

But I found something strange, if I manually go into the formula bar of D2 for example, I click on it, then I press enter and now my column looks like this.

|     D      | 
|15/01/2013  |
|  20/01/2013|
|01/02/2013  |

And if I do this for all the values in my column then

Range("D1").Value <= Range("D2").Value will return a True statement.

Can someone explains me why is Excel doing this and how I can automatically have the exploitable date in my column because I won't manually change 500 rows.

Thanks

MacroShadow MacroShadow · Accepted Answer · 2015-03-29T13:31:24

You don't have to manually change 500 rows. You can use the Text to Columns feature to correct the problem.

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

Excel doesn't recognize dates

2 Answers