Date formats?

[gg]

I am having trouble getting cells to read as dates.

Currently I have in column A

1/10/2004
thru
2/6/2004

I am wanting to sort these asending however, it is only reading the
first letter of the date.

This data was built by and web query that seems to have chaged the format to something else. ( Raw Text, Not date number)

Example:
1/10/2004 as text -->37996 = OK
1/10/2004 as text--> 1/10/2004 = NG

Any ideas how to make these read as a date.
Format cells--> date is already done.

 

[Positive Parrot]

gg,

I've had a similar experience and used the VBA DateValue function to get the dates in the right format.

For example if your problem date is in cell A1 try a macro

Sub ChangeDate
Range("B1").Value = DateValue(Range("A1").Value)
End Sub

and format cell B1 to be a date in whatever format you want.

Regards,

PP

 

[gg]

That works great..

One more question.
How do I get it to do about 1000 in a row is there an easy way?

 

[gg]

bttt

 

[Greg Truby]

Select all the cells, then use Data | Text to Columns... fixed (though delimited would work too if you do it right) and then select the appropriate Date Option. That'll force it over to dates.

 

[Positive Parrot]

gg,

Greg's solution is neat but if you want to extend my macro solution from earlier then try something like

Sub ChangeDates

For i=1 To 1000
Range("B" & i).Value = DateValue(Range("A" & i).Value)
Next i

End Sub

Regards,

PP