[Solved] Sorting dates in DD/MM/YY format by year

[kcharles520]

I have a bunch of spreadsheets and the dates are all listed in DD/MM/YY format, ie: 16/8/15.
Sorting the dates by year is a big part of the research I'm doing, but when I choose "sort" it arranges them by day instead of year.

Is there anyway to sort by year instead? If not, is there an easy way to instantly switch all the dates to the standard MM/DD/YY format?

[RoryOF]

Is there anyway to sort by year instead? If not, is there an easy way to instantly switch all the dates to the standard MM/DD/YY format?

Keep dates in ISO standard format (YYYYMMDD) and they sort correctly
"standard MM/DD/YY" is only a local standard (i.e., in America).

[RusselB]

Since dates/times are actually stored as numbers, if you need the dates in MM/DD/YY format, you can always apply that format to the cell(s) that contain the dates after the sorting has been completed per Rory's suggestion.

[kcharles520]

Is there anyway to sort by year instead? If not, is there an easy way to instantly switch all the dates to the standard MM/DD/YY format?

Keep dates in ISO standard format (YYYYMMDD) and they sort correctly
"standard MM/DD/YY" is only a local standard (i.e., in America).

I received the spread sheets from an external source they all have this weird, DD/MM/YY format.
I'm guessing there is no way to get them to YYYYMMDD or MM/DD/YY at this point?

[RusselB]

You should be able to just apply the ISO standard format to the dates before sorting... similar to what I posted earlier.

[RoryOF]

Select the dates and /Format /Cells picking a Year Month Day format. Then /Tools /Sort, and when finished select the cells again /Format /Cells and pick the display format you require. Best to work on a copy until you have this right.

[kcharles520]

Hmm I tried switching to YYYY/MM/DD format but when I sort it still arranges by date, ie: 1 to 31 instead of the year.

Also when I change the "format" it just keeps the row DD/MM/YY visually...so it says it changes them, but physically they look the same.
I did export this whole thing from CSV, then saved to .ODB...perhaps that is the issue?

Definitely stumped.


EDIT: I think I know why it isn't working....look at the photo below. All of the dates are entered with an apostrophe thing for some reason, ie: '01/02/08. It doesn't show the apostrophe thing in the column itself, but they all have that. Perhaps that's why the format change isn't applying properly?

[Villeroy]

Your data are text. The text "02/03/2011" is not a date. It is a sequence of characters with 8 digits and 2 slashes. Please do not enter/import/paste any text if you want numbers. Yes, the ' indicates a text like a = indicates a formula.
And it is impossible to tell if "02/03/2011" refers to the 2nd of March or 3rd of February.

[Lupp]

It's a mess! Not only that the dates are text, and that they are formatted in a basically ambiguous way because of the stubbornness of people, they also are given with 2-digit-year. Having the "Y2k" hystrerics still in mind I find it hard to believe that we should again be ready for such an idiocy.

However, there should be a way to fix things.
1. The only date format guaranteed to be recognised by ODF specifications independent of the locale is "YYYY-MM-DD", with 4-digit year to make it clear. (Near ISO 8601)
2. If the dates like 01/02/08 shown in a recent post are assured to be DD/MM/YY with 2-digit-years in a given century, say, the twenty-first, we can disambiguate and recognise them as follows:
Be such a date in A1. Enter the formula

Code: Select all

=VALUE("20"&RIGHT(A1;2)&"-"&MID(A1;4;2)&"-"&LEFT(A1;2))

into B1 and apply the number format code "YYYY-MM-DD" (using the equivalent letters in your locale if necessary) to this cell. Fill down from this cell as far as needed. You get the dates this way in their numeric representation, usually used by spreadsheets. They will sort correctly now, too.

Possible problem 1:
In A1 an apostrophe is actually shown. This should mean your locale/settings do not recognise a nn/nn/nn pattern as something numeric.
Possible problem 2:
Some of the entries assumed being dates have only 1 digit in a day or a month place, or 4 digits in the year position.
Possible problem3:
Some of the entries were yet recognised as being numeric.
If one of these problems actually is showing, pleas ask for help again.

Down with localising, as far as it only causes additional problems!
Down with idiotic "abbreviations" which may save a typist some milliseconds now and then and will cost hours or even much more for repairs as soon as they start to fail - or have already caused damage in the dark.

[kcharles520]

That formula worked brilliantly Lupp! You're my hero, now I can finally do the work I need to do lol. Everything sorts perfectly now.