Sorting when using dd.mm.yyyy format

**cawsandboy** · 11-30-2011, 11:02 PM

hi am getting obvious result when I try to date sort my worksheet using the above, (sort results in sorting by day,then month and finally by year.)

How can I sort by year,month and day without tedious correction please??

Any help most welcome

best wishes cawsandboy

**teylyn** · 12-01-2011, 12:11 AM

Hello,

what are "obvious" results?

If the cells contain real dates, not text that just looks like a date, then sorting the cells will render a ascending or descending order of the dates, depending on the sort order selected.

If the data is text, then alphabetical sort order will be applied.

So, make sure the dates are real dates. How you enter "real dates" depends on the regional settings of your computer.
How you display the dates can be influenced by formatting the cells. For example, if your computer's regional settings have the date format as

mm/dd/yyy

you must enter the dates like this

12/25/2011

and it will be a real date. You can then use a custom format to display the date as

25.12.2011

with the custom format

dd.mm.yyyy

Sorting will now work in chronological order.

However, if you simply enter

25.12.2011

into a cell, Excel will not recognise this as a date (since the regional settings use the / delimiter and the month in the first position). The value will be treated as text and all sorting will be alphabetical, rather than chronological.

cheers,

**cawsandboy** · 12-01-2011, 12:30 PM

Hi Terlyn, thanks for your previous reply. Have re formatted column containing dates to dd/mm/yyyy as you advised. All dates in 19th century changed fine. Dates in 20th century went alpha (eg 20th February 1902). I individually corrected these and all looked good.
When attempting to re-sort my worksheet from Alphabetic to by date (column B) all the 20th C dates appeared first in expected order (but they should have been at bottom)
The 19th Century dates returned to previous 'alphabetical' order - with number starting with '0' first.

Have I got a glitch here?

Copy of file attached for any suggestion you may have

hoping to hear

regards cawsandboy

**teylyn** · 12-01-2011, 03:56 PM

Excel cannot store dates before 1900. Dates are internally stored as numbers, with 1 representing 1/1/1900. Anything before that, i.e. any number smaller than 1 can not be used to represent dates. Manually entered dates that are before 1900 will be interpreted as text.

You will need some workarounds to achieve a proper sort. For example, you could extract the year, month and day portions as numbers and then do a three level sort.

See attached.

cheers,