Forum Discussion
Solved
Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only
How can I:
Sort by table chronologically by date in MS Excel when some date are DD/MM/YY and some are YYYY only
Hi CliveHerbert
In the below example, when there's no dd/MM but YYYY only (length=4) the latter is used to construct a Date like 01/01/YYYY:
in C2:
=SORTBY( A2:A7, IF(LEN(A2:A7) = 4, DATE(A2:A7,1,1), A2:A7) )
27 Replies
Hi CliveHerbert
In the below example, when there's no dd/MM but YYYY only (length=4) the latter is used to construct a Date like 01/01/YYYY:
in C2:
=SORTBY( A2:A7, IF(LEN(A2:A7) = 4, DATE(A2:A7,1,1), A2:A7) )In Microsoft Excel, you can sort a table chronologically by date even if some dates are in the format DD/MM/YY and some are in YYYY format. Here's how you can do it:
Select the Data Range: Click and drag to select the entire range of data that includes your dates.
Open the Sort Dialog Box: Go to the "Data" tab on the Excel ribbon, then click on "Sort" in the "Sort & Filter" group. This will open the Sort dialog box.
Add a Custom Sort Level: In the Sort dialog box, you should see a list of columns in your data range. Click on "Add Level" to add a new level for sorting.
Choose the Column: In the "Sort by" dropdown for the new level, choose the column that contains your dates.
Select the Custom List: In the "Order" dropdown for the new level, select "Custom List."
Create a Custom List: Click on the "Custom List" button next to the "Order" dropdown. This will open the Custom Lists dialog box.
Enter the Custom List: In the "List entries" box, enter the date formats you have in your data. For example, if you have DD/MM/YY and YYYY, you would enter them in separate lines like this:
- DD/MM/YY
- YYYY
Apply the Custom List: Click "Add" to add the custom list to Excel's list of custom sorts, then click "OK" to close the Custom Lists dialog box.
Sort the Data: Back in the Sort dialog box, make sure the "Sort On" dropdown is set to "Values" and the "Order" dropdown is set to "A to Z" (ascending). Click "OK" to apply the sort.
Excel will now sort your dates chronologically, taking into account both the DD/MM/YY and YYYY formats you've specified in the custom list.
Very much appreciated & well documented. I'm aware of this approach, however this won't do what they expect, except if missed something...
In the attached workbook there's a sample dataset and the expected result. If you can get the expected result with a custom sort, please (re)explain how you do it, you'll probably save their life
- Hi I am trying to help Clive Herbert but I don’t understand where to enter the formula
- Hi Lz
Thank you
