date formats

[genehollick]

I have a sheet with approx 1000 records. In one of the columns the description contains a date in the format m-dd-yyyy. I need to sort on that date, but it apparently needs to have the date in the format YYYY-mm-dd to sort properly. How can I sort correctly or convert the dates to the proper format?

[Don]

Hi and welcome to the forum!

Have you tried just selecting all of the cells with dates, or the entire column, and formatting the cells?

If not, just select the dates, right-click > Format Cells > Number tab > look in either the Date or the Custom section. If you don't see the correct date format there, stay in the Custom category and just enter the desired format under where it says "Type:."

hth!

[Sisyphus]

Hi,

Excel stores dates as numbers, for example, the nth day since January 1, 1901. The format you see, as dd.mm.yy or mmm/dd/yyyy is only what you see, not necessarily what Excel has stored in your worksheet. Sorting is done using the number. So, the output format doesn't matter, and any problem you may have can't be cured by changing the output format.

Since your data don't sort properly chances are that they aren't in date format. It is common practice to convert dates to strings to prevent Excel from applying regional interpretations to them which can lead to confusion on dates like 2/12/11 if the input wasn't done with the same settings as the viewing. To find out which format your dates have format a cell as "Number" using the cell formatting Don has described. If the date in the cell changes to a number the cell can be sorted as a date. Else, it can be sorted only as a string where 11 turns up between 1 and 2 instead of at the end.

You can convert strings to dates. Perhaps this can be done by simply formatting the cell as date, as Don has suggested. If that doesn't work try formatting another cell as date and put this kind of formula in it:

Code: Select all

 =DATEVALUE(A1)


This should convert the string in A1 to a sortable date. But beware of dates that Excel might not convert correctly. There will be no error message and your November 2 ends up as February 11 - all correctly sorted on that premise.

I hope this helps. Have a great day!