Hi All,



Now I don't think that this is possible, but if in doubt ask the experts.

I have a list that I want to sort either of two ways, one is by date and the other is alphabetical. The alpha works just fine, but when it comes to the dates I have problems, which is the part that I don't think is possible.

As you can see, the date can be dd/mmm/yy - mmm/yy - yyyy, and this is where the issue is.

Any ideas?

Best Rgds,
Dave R.

Attached Files

Sorted Dates.xlsx (10.3 KB, 12 views)

You could try with a helper column (that can be hidden) then using " Sort by" ( I suppose you will have no dates before 1941?)
Or a Power Query solution

Attached Files

Copy of Sorted Dates.xlsx (5.9 KB, 7 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Dave,

Unless you're storing that date as text, all dates in Excel have an underlying numerical value. For example March 10th, 2019 in your first line, I'm presuming, is 43534. And even if you changed the cell's number formatting to display that as 19-Mar-2019, it's still that numerical value. So, yes, you should absolutely be able to sort on the dates. I just typed in three cells formatted American style, m/d/yy, and then dd-mmm-yyyy, and sorted them.

1/2/2022
11-Jan-2022
1/23/2022
14-Feb-2022
2/15/2022
01-Mar-2022

Ah, but that's where my problem lies. If you have only the year, say 2019, that's what the number is whereas March 10th 2019 has a numerical number of 43534.

Hi Pecoflyer,

You're talking to an oldie who's Excel knowledge is pretty basic. I thought about putting in a separate column with number 1, 2, 3 etc., and hiding that but how can I get the sheet to sort by that column. For clarity, there are other columns that rely on each row so when I insert the sort filter they all become live, so to speak. The only other column that I need to sort by is the alpha one, and that sorts OK. Looked at 'Sort By' and that might do the trick, but how would I go about inserting that based on the above.

Your main problem is, instead of date you have 2 entries with year numbers (integers)

Number 1965 has same value as date 18.05.1905, and number 1985 has same value as 07.06.1905.

In Excel, a date is really the number of days from 10.01.1900 (or from 31.12.1899 -
depending how you calculate this number)!

Yes, I thought I was pushing the bounds of possibilities, still it was worth a try as I know that there are some really smart bunnies out there.

Thanks anyway.

Could you be a bit more specific please, I don't understand what's wrong
Have you seen how my solution works?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Easy to cope with. Supposing there are no years before 1941, say, just check if the "year" in the data ( 1965, say) is smaller than 15000 ( somewhere in 1941), the build a fake date with the DATE function

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

A simplest solution - replace all non-date numeric entries (year numbers) manually with dates. E.g. instead 1965 enter a date like 01.01.1965, and instead 1985 enter 01.01.1985.
In case you want to display them as year numbers, simply format those cells as custom "yyyy" (but this is not obligatory). Now sorting by date returns exactly what you want.

In case those data are read dynamically form some database, the solution can be more tricky, involving a helper column where all non-date entries are replaced with calculated dates, and date entries with copy of dates, using some complex formula. Then you can sort by this helper column.

If you take a minute to open the file I posted that is EXACTLY what I did.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi Pecoflyer,

I apologize most profusely, I did open the file but didn't take in exactly what was happening, sorry. OK that does sort things as I wanted.

Next question, when you set up a sort filter all columns are selected. Is there a way of disabling one columns sort filter without effecting the use of the others. As you can see, if you sort the Date column it doesn’t come out the same, because it’s looking at the date number.

Best Rgds,
Dave R.

I don't really understand what you are trying to achieve (BTW post #11 was addressed to Arvi)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

If you click the Helper sort button cloumn B sorts fine, if however you hit the Date sort button everything goes out of whack. Now in my spreadsheet I just need to sort by date or by letters. When you set up a sort filter all colums are live, so if the Date sort button is clicked by mistake - trouble.

Pecoflyer calculated year number for Helper column. I changed his/her file so a date is calculated instead. And you never must sort by Date column - only by Name or Helper columns!

Now some rules:
To sort the table by single column, activate a single cell in this column, and then either click on filter box of same column, and select Sort Oldest to Newest/Newest to Oldest, or activate Sort Filter from Home menu and then select Sort A to Z/Z to A or selct Custom Sort, and define sorting rules there (the last option works with any single cell in table activated);
To sort the table by several columns at once, activate any single cell in table, and then use Custom Sort and add as many rules as you need.

NB! When activating sorting, you must have a single cell in table activated! Whenever a block of sell is activated, the sorting applies only to this block! I.e. when you have a column of data selected, and sort this column, only data in this column will be sorted - all other columns remain in old order, and your table is in bucket!

Attached Files

SortedDates.xlsx (11.5 KB, 5 views)