Ask Your Question

Parraig's profile - activity

2016-11-30 16:16:25 +0200 received badge  Editor (source)
2016-11-30 14:56:22 +0200 answered a question Chronological date columns

I had great difficulty sorting columns by date.

Data - Sort - with a column with date values would only sort by digit order. So UK Irish date format of DD/MM/YYYY doesn't work.

Changing the date format Format - Cells - Date wouldn't work - the format remained the same. Eventually I created a new column, and entered

=RIGHT(C513,4)&MID(C513,4,2)&LEFT(C513,2)

which converted 29/12/2009 in cell C513 to 20091229 in cell B513, but with the formula in each cell:- I then Pasted SPECIAL (first tried numbers - but that produced blank cell) Edit - Paste Special - Text This gave a text version of the original Date

which then sorted correctly.

Not necesary - but I created a new column with : =VALUE(B513) which gave a number from the text in B513