Home

Help sorting database by date

%3CLINGO-SUB%20id%3D%22lingo-sub-830655%22%20slang%3D%22en-US%22%3EHelp%20sorting%20database%20by%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830655%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%3C%2FP%3E%3CP%3EI%20have%20a%20database%20which%20has%20a%20column%20with%20enrol%20date.%26nbsp%3B%20I%20am%20in%20NZ%20and%20our%20date%20formate%20is%20DD%2FMM%2FYYYY.%3C%2FP%3E%3CP%3ECurrently%20the%20database%20is%20sorted%20by%20the%20number%20not%20date%2C%20ie%2001%2F02%2F2019%20comes%20after%2001%2F01%2F2019.%26nbsp%3B%2002%2F01%2F2019%20will%20come%20after%2019%2F01%2F2019%20!!%26nbsp%3B%20I%20cannot%20change%20or%20sort%20anything%20because%20of%20an%20issue%20with%20the%20size%20of%20a%20merge%20cell.%26nbsp%3B%20I%20have%20tried%20to%20find%20the%20merge%20cell%20using%20the%20find%20feature%2C%20but%20the%20result%20is%20there%20are%20no%20merge%20cells!%26nbsp%3B%20Still%20unable%20to%20sort%20though%20as%20getting%20the%20same%20error%20message.%26nbsp%3B%20Nasty%20loop%20I%20can't%20seem%20to%20find%20my%20way%20out%20of.%26nbsp%3B%20Please%20can%20anyone%20help%20me%3F%26nbsp%3B%20Probably%20a%20very%20simple%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20so%20much%3C%2FP%3E%3CP%3ESmiles%2C%20Janey%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-830655%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831034%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20sorting%20database%20by%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400819%22%20target%3D%22_blank%22%3E%40janey273%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20unmerge%20the%20cells%20select%20your%20column(s)%20and%20click%20on%20Merge%20%26amp%3B%20Center%20icon%20on%20ribbon%2C%20it's%20not%20necessary%20to%20search%20which%20exactly%20cells%20are%20merged%20in%20the%20range.%3C%2FP%3E%0A%3CP%3EAnd%20it%20looks%20as%20your%20dates%20are%20actually%20texts.%20To%20convert%20to%20dates%20select%20the%20column%20with%20dates%2C%20Data-%26gt%3BText%20To%20Columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831055%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20sorting%20database%20by%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831055%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EHighlight%26nbsp%3Bthe%20range%20of%20cells%20to%20be%20sorted.%20In%20this%20example%2C%20cells%20A2%20to%20E12%20are%20selected.%3C%2FLI%3E%3CLI%3ESelect%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EHome.%3C%2FLI%3E%3CLI%3ESelect%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3ESort%20%26amp%3B%20Filter%26nbsp%3Bto%20open%20the%20drop-down%20list.%3C%2FLI%3E%3CLI%3ESelect%26nbsp%3BCustom%20Sort%26nbsp%3Bto%20open%20the%20Sort%20dialog%20box.%3C%2FLI%3E%3CLI%3EPlace%20a%20check%20next%20to%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EMy%20data%20has%20headers.%3C%2FLI%3E%3CLI%3EUnder%20the%20Column%20heading%2C%20select%20the%26nbsp%3BSort%20by%26nbsp%3Bdown%20arrow%20and%20choose%26nbsp%3BNamefrom%26nbsp%3Bthe%20drop-down%20list%20to%20first%20sort%20the%20data%20by%20the%20Name%20column.%3C%2FLI%3E%3CLI%3EUnder%20the%20Sort%20On%20heading%2C%20leave%20the%20setting%20as%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3ECell%20Values.%20The%20sort%20is%20based%20on%20the%20actual%20data%20in%20the%20table.%3C%2FLI%3E%3CLI%3EUnder%20the%20Order%20heading%2C%20select%20the%20down%20arrow%20and%20choose%26nbsp%3BZ%20to%20A%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20sort%20the%20Name%20data%20in%20descending%20order.%3C%2FLI%3E%3CLI%3ESelect%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAdd%20Level%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20add%20a%20second%20sort%20option.%3C%2FLI%3E%3CLI%3EUnder%20the%20Column%20heading%2C%20select%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EThen%20by%20down%20arrow%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%20choose%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAgeto%20to%20sort%20records%20with%20duplicate%20names%20by%20the%20Age%20column.%3C%2FLI%3E%3CLI%3EUnder%20the%20Order%20heading%2C%20choose%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3ELargest%20to%20Smallestfrom%20the%20drop-down%20list%20to%20sort%20the%20Age%20data%20in%20descending%20order.%3C%2FLI%3E%3CLI%3ESelect%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EOK%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20close%20the%20dialog%20box%20and%20sort%20the%20data.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
janey273
Occasional Visitor

Hi there

I have a database which has a column with enrol date.  I am in NZ and our date formate is DD/MM/YYYY.

Currently the database is sorted by the number not date, ie 01/02/2019 comes after 01/01/2019.  02/01/2019 will come after 19/01/2019 !!  I cannot change or sort anything because of an issue with the size of a merge cell.  I have tried to find the merge cell using the find feature, but the result is there are no merge cells!  Still unable to sort though as getting the same error message.  Nasty loop I can't seem to find my way out of.  Please can anyone help me?  Probably a very simple solution.

 

thank you so much

Smiles, Janey

2 Replies

@janey273 

To unmerge the cells select your column(s) and click on Merge & Center icon on ribbon, it's not necessary to search which exactly cells are merged in the range.

And it looks as your dates are actually texts. To convert to dates select the column with dates, Data->Text To Columns.

 

  1. Highlight the range of cells to be sorted. In this example, cells A2 to E12 are selected.
  2. Select Home.
  3. Select Sort & Filter to open the drop-down list.
  4. Select Custom Sort to open the Sort dialog box.
  5. Place a check next to My data has headers.
  6. Under the Column heading, select the Sort by down arrow and choose Namefrom the drop-down list to first sort the data by the Name column.
  7. Under the Sort On heading, leave the setting as Cell Values. The sort is based on the actual data in the table.
  8. Under the Order heading, select the down arrow and choose Z to A to sort the Name data in descending order.
  9. Select Add Level to add a second sort option.
  10. Under the Column heading, select the Then by down arrow and choose Ageto to sort records with duplicate names by the Age column.
  11. Under the Order heading, choose Largest to Smallestfrom the drop-down list to sort the Age data in descending order.
  12. Select OK to close the dialog box and sort the data.

Hope this helps!

 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies