CODEWITHSUNDEEP
excelvba
Tom
Tom

Reputation: 169

Excel doesn't recognize dates

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

Upvotes: 0

Views: 2791

Answers (2)

MacroShadow
MacroShadow

Reputation: 71

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

Upvotes: 1

Bathsheba
Bathsheba

Reputation: 234635

It's to do with implicit coercion of string types to numeric types. (Excel by the way does not have a specific date type; it uses its number type to represent dates).

Year function will implicitly convert the supplied argument to a numeric type; so removing any extraneous spaces.

The .Value function acting on the Range, however, will not do that.

Consider using the DATEVALUE function in an adjacent column which will convert text to a date (albeit in a locale specific manner) and perform comparisons on that.

Upvotes: 1

Related Questions