Ask Your Question

why can't I sort by date properly?

asked 2018-09-07 03:48:53 +0200

mary gawle gravatar image

I have checked the format as "date" for the column, but 1-1-2018 is followed by 12-1-2018 instead of 2-1-2018 when I sort. It is sorting as just numbers, not as dates. Am I going to have to redo all the dates??? Isn't that what formatting it is supposed to handle?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-09-07 06:39:21 +0200

updated 2018-09-07 06:40:29 +0200

Dates will only sort properly if they are dates. Your data seems to be the text. Entering something into a cell will be checked for what data type it looks like (is that number? date? percent?), and the data will be stored with the detected data type. The date is the most difficult here (because there are so many different date formats out there); and there is a dedicated setting determining which input formats are accepted as dates: OptionsLanguage SettingsLanguagesDate acceptance patterns, and they are unrelated to cell format options. Of course, using the ISO date format (2018-01-28) would be detected.

After the data is entered and its type is detected, changing cell formatting will never change the data type. Formatting is the way how the data is shown, not how it's treated. So even if you mark your cells as date, if they have contained text, they will continue to have text, and no date sorting will be applicable.

To change type of existing data, you need not to change formatting, bit to convert - that is done using DataText to Columns, and select the proper data type for the columns by right-clicking the columns headers in the dialog (you'd need Date (DMY)).

image description

edit flag offensive delete link more


Thank you very much. This worked for me.

After making the Text to Column change you described, I then changed the Format for the cells I wanted to convert to date and it sorted perfectly after that. Thanks!

Bunch gravatar imageBunch ( 2020-09-23 23:27:32 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-07 03:48:53 +0200

Seen: 5,368 times

Last updated: Sep 07 '18