sorting by date in excel

[pattyf]

I've imported data that contains a column of dates, mm/dd/yyyy, but can not
get this column to sort correctly in date order. I'm on version 2002. Any
ideas?

 

[Mike H]

Are you sure they are dates and not text that looks like a date?
=ISTEXT(A1)
should return false for a date

Mike

 

[Kevin B]

Insert a helper column to the right of your errant dates and enter the
following formula in the first blank cell of the new column (substituting A1
with the cell containing the text date):

=DATEVALUE(A1)

Copy the formula down as far as necessary and then copy your formula column.

Move the the first cell of the original date column, click EDIT in the menu
and select PASTE SPECIAL and select the VALUES option button & click OK. You
can then delete your helper column.

You might have to format the column for dates, as it converts the text date
to a serial date number.

 

[pattyf]

Kevin, you're a wizard! Thanks for your help!

Insert a helper column to the right of your errant dates and enter the
following formula in the first blank cell of the new column (substituting A1
with the cell containing the text date):

=DATEVALUE(A1)

Copy the formula down as far as necessary and then copy your formula column.

Move the the first cell of the original date column, click EDIT in the menu
and select PASTE SPECIAL and select the VALUES option button & click OK. You
can then delete your helper column.

You might have to format the column for dates, as it converts the text date
to a serial date number.

 

[Bob I]

Are you sure it's dates and not really text that looks like a date?