Excel date sorting is one of the most useful features of MS Excel. Whether you wish to organize your relatives’ birthdays so you can send them a greeting card, organize your weekly budget transactions for the fiscal year, or perhaps keep track of the sales figure every month. All these things can be done effectively using the Excel date sorting feature in MS Excel.  

The beauty of this feature is that you have the liberty to organize your entries in accordance with the date. You can sort the date by day, week, month, or year. Alternatively, you can even arrange the dates in ascending or descending order.  

Sorting the dates in chronological order might be helpful when you have a huge data set to evaluate. Excel includes certain built-in tools to accomplish the task, but occasionally they fall short of our expectations.  

In this blog, we will provide a comprehensive guide to performing Excel date sorting without any hassle. So, without further ado, let us get started with the guide.

Different methods for excel date sorting

Although Excel’s built-in sort capabilities are strong and better equipped, they occasionally fail to sort dates properly. This post will show you a few practical tips for organizing Excel data by date in a meaningful manner without affecting the quality of your data.

We will also showcase how these methods can be useful for customer success professionals working in B2B organizations.

Dates arranged chronologically 

Excel makes it remarkably simple to organize dates chronologically. You select the default Ascending Sort option:

• The dates you want to order chronologically should be chosen.
• Select Sort Oldest to Newest under Sort & Filter on the Home Tab’s Formats group. Alternatively, you can use the Sort & Filter group’s A-Z option under the Data tab.

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

There are times when customer success professionals need to prioritize the accounts based on dates in chronological order. This can be because they want to send subscription reminder emails. It can even be to keep a check on the payment date of a particular account.

Sorting dates in a column 

It is fairly easy to arrange a column of dates if that is all you are worried about. Let us say you need to sort only the dates in Column A of the dataset below. The steps to date-order this data are as follows:

• Choose the column containing the dates you wish to arrange by.
• Locate the – Sort and Filter – group under the Data tab. You have several options for sorting the chosen data in this category. Depending on your needs, you can choose either “Sort Oldest to Newest” or “Sort Newest to Oldest” for a straightforward sorting operation.
• You might be asked if you want to keep your current option or expand it to include the other columns. We selected the second option since we currently want to sort the selected column.
• Select Order from the menu.

That is it; based on your choice, the selected column will be sorted from newest to oldest date or from oldest to newest date.

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

For customer success, professionals must prioritize the accounts from the oldest to the newest. It is during such times when this excel date sorting method is utilized.

Sorting data in multiple columns using date 

In more practical terms, you should not sort by a single column. Let us say you need to sort a dataset like the one below based on the date in Column A. The steps to sort multiple columns using the date column are as follows:

• Handpick all the cells that you wish to sort.
• Inside the Data menu ribbon, handpick the ‘Sort’ button (in the Sort & Filter group). This will open the Sort dialog.
• There are 3 dropdown lists in this specific dialog.
• Using the first dropdown menu, you can choose the column to organize the data. Make sure to select the column that contains your dates.
• You can choose what to order from the second drop-down menu. For this, make sure the “Value” option is chosen.
• You can choose the dates’ sorting order using the third dropdown menu (oldest to newest or newest to oldest). Even better, you can design your unique sort order. However, let us save that for a different tutorial. Decide the order you want your dates to be sorted.
• After that, click OK.

Depending on what you provided, this should sort your dates in either ascending or descending order. Additionally, move the cells.

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

There are times when customer success professionals use different columns to define the date on which the account started and the date on which its subscription will end. When the customer success professional wishes to sort these two columns based on their needs, this excel date sorting method is used.

With columns of text, format your cells in date format 

The steps for converting text to date in Excel using Text to Columns are as follows:

• Choose the column containing the dates you wish to order by.
• Click Text To Columns under the Data tab (in the Data Tools group). The Text to Columns dialogue will now open.
• You can choose whether the data in the chosen cells are delimited in this dialogue box’s option. If the phrase “delimited” is new to you, it refers to the character that designates how data in cells is divided, such as when sections of your date are separated by the symbol “/.” In this case, a ‘/’ character serves as the delimiter. Make sure that the ‘Delimited’ alternative is preferred.
• Select Next
• Ensure all of the checkboxes in the dialog’s subsequent section are unchecked. This is because the separator in your situation is pretty much irrelevant.
• Choose Next.
• The important thing is this. You can choose the format of your date in this section of the dialogue. Choose “Date” using the radio button (under Column Data Format). You can choose the format in which your dates will be presented by using the dropdown list located next to this option. Like in our example, you can choose the DMY option if your dates are in the dd/mm/yy format. There are other additional options available; pick the one you like best.
• Click Finish to finish.
• Use the =ISTEXT() function once more to confirm whether your cells have been successfully transformed to dates. If you receive a FALSE result this time, Excel now interprets your cells as dates rather than words.

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

There are times when customer success professionals wish to convert the data they have at their disposal into data; this is where this excel date sorting method is used.

Excel data sorting by date 

To choose the dates you want to sort by, drag down the column. Click the Oldest to Newest or Newest to Oldest buttons under the Sort & Filter section of the Home tab.

You can rearrange the entire table using the Excel sort options, not just a single column. The trick is to expand the selection when prompted to sort records by date while maintaining the rows.

The specific steps to sort data in Excel by date are as follows:

1. Select the dates in your spreadsheet without selecting the column headers.

2. Select Sort Oldest to Newest under Sort & Filter on the Home tab.

3. You will see the Sort Warning dialogue box. Click Sort: and select the default Expand the selection option.

4. You have achieved the objective. All the rows are maintained together once the records have been sorted by date:

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

Customer success professionals sometimes need to maintain the accounts based on the date they were incorporated. This is where this excel date sorting method is utilized.

Monthly data sort

For example, when collecting the anniversaries of your coworkers or relatives, you might choose to organize dates by month rather than the year. In this situation, even if your cells display only the month or the month and day, Excel’s default sort feature will not function since it always considers the year.

The solutions are adding a helper column, removing the month number, and sorting by that column. Use the MONTH function to determine a month from a date.

The formula shown below is used to get the month number from the date in B2:

=MONTH(B2)

Tip: Set the General format to the formula cells if the result is shown as a date rather than a number.

Sort your table now using the Month column. For this, choose the month numbers (C2–C8), then click Sort & Filter > Sort Smallest to Largest. When Excel prompts you to widen the selection, do so. If everything is done correctly, you will obtain the following outcome:

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

When the customer success wishes to sort the birthdays of the accounts based on the month of the year, they use this excel date sorting method.

Birthdays sorted by month and day 

The best way to arrange dates for a birthday calendar is to group them by month and day. As a result, you require a formula that would extrapolate months and days from birthdates.

The Excel TEXT function is useful in this situation since it can turn a date into a text string with the desired formatting. The format code “mmdd” or “mm.dd” will suffice for our needs.

The formula looks like this when the source date is in B2:

=TEXT (B2, “mm.dd”)

The data will then be organized in order of the days in each month after you sort the Month and day column from greatest to smallest.

The DATE formula can be used to get the same output as follows:

=DATE(2000, MONTH(B2),DAY(B2))

The formula creates a list of dates by taking the month and day from the actual date in B2 and substituting a fictitious year, in this case, 2000, though you can use any other year.

All of the dates should be in the same year and then be arranged chronologically. The dates will be ordered by month and day because the year is the same, which is precisely what you want.

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

When the customer success professional wants to create a database of the accounts based on their birthdays sorted by date and month, this excel date sorting method is used.

Data sorting by year 

The simplest method for sorting dates by year is to use Excel’s ascending sort (Oldest to Newest) option to arrange dates in chronological order.

As seen in the screenshot below, this will first sort dates by year, then by month, and finally by day.

If for any reason, this arrangement does not satisfy you, you can add a helper column that uses the YEAR formula to separate the year from the date:

=YEAR(C2)

Once the Year column has sorted the data, the dates are only sorted by year, ignoring months and days.

Advice: To sort dates by day while ignoring months and years, use the DAY function to extract the day, then sort by the Day column:

=DAY(B2)

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

When the customer success professionals wish to separate the year from the date of the first subscription of the account, this excel date sorting method is used.

Data by weekday, sorted 

As in the preceding instances, you will also require a helper column to sort the data by weekday. In this instance, we will be sorting by the helper column after adding the WEEKDAY formula, which yields a number according to the day of the week.

This is the formula to use for a week that runs from Sunday (1) through Saturday (7):

=WEEKDAY(A2)

Here is the correct one if your week runs from Monday (1) to Sunday (7):

=WEEKDAY (A2, 2)

where the date is located in cell A2 (A2).

Using the first formula, we arrived at the following outcome for this example:

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

When customer success professionals wish to remember the day of the week the account’s subscription is due, they use this excel date sorting method.

Sort by the month names that were typed in 

Applying Excel’s ascending sort could be problematic if you have a list of month names in text form rather than dates structured to display only months. It will organize the names alphabetically rather than by month from January to December. Here, a specific kind will be beneficial:

• If you wish to sort the records by month name, select the records.
• Click Sort under the Sort & Filter group on the Data tab.
• Do the following in the Sort dialogue box:
• Choose the column name that holds the months’ names under Column.
• Select Cell Values under Sort on.
• Go to Order and choose Custom List.

1. Depending on how the months are listed in your worksheet, use either full month names (January, February, March,…) or abbreviated names (Jan, Feb, Mar…) in the Custom Lists dialogue box:

2. To close both dialogue windows, click OK twice.

3. Done! Your data has been arranged chronologically rather than alphabetically by month name:

Tip: In the Custom Lists dialogue box, choose either complete names (Sunday, Monday, Tuesday,…) or short names (Sun, Mon, Tue…) to sort by the names of the days of the week.

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

When the customer success professional needs to remember the month of a particular account’s subscription due date, they use this excel date sorting method.

Excel auto sort by date

The Excel Sort tool handles a range of issues, as you have seen. Its lack of dynamic nature is its lone negative. In other words, you will need to re-sort your data every time something changes, or new data is introduced. Maybe you’re trying to figure out if there is a method to sort data automatically each time a new date is entered so that it is always in the right order.

 The most effective approach to do this is by utilizing a macro. The following data will be automatically sorted by date and placed in chronological order using the code examples found below.

Macro 1: Auto-sort each time a worksheet is modified

Every time something is changed in the worksheet, this macro is run.

 It is expected that the dates you wish to sort by are in column C, starting in C2, and that your data are in columns A through C. Row 1 is also considered to have headers (Header:=xlYes). Make the changes below if your records are in different columns:

• To your target range’s upper left cell, change the A1 reference (including headers).
• Change the C2 reference to the cell with the date at the top.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    Range(“A1”).Sort Key1:=Range(“C2”), _

        Order1:=xlAscending, Header:=xlYes, _

        OrderCustom:=1, MatchCase:=False, _

        Orientation:=xlTopToBottom

End Sub

Macro 2: When modifications are made to a certain range, auto sort

Re-sorting with even the slightest modification in the sheet could be challenging if you are working with a large worksheet that is filled with a lot of data. In this situation, it makes appropriate to restrict the macro’s triggering to changes that take place within a particular range. Only when a modification is made to column C, which holds the dates, does the following VBA code sort the data.

A step-by-step guide to add the macro to your worksheet

The code should be added to the worksheet where you want to sort the data because both macros are executed automatically whenever a worksheet is changed (Sheet1 in this example). This is how:

• For the VBA Editor to launch, press Alt + F11.
• Double-click the sheet where you want the auto sort to appear in the Project Explorer on the left.
• Paste the code in the Code window.

Using a formula to auto-sort dates

Consider a scenario in which you have a list of dates and would want them to be automatically organised in chronological order in a second column, next to the original list. This can be accomplished using the array formula below:

=IFERROR(INDEX($A$2:$A$20, MATCH(ROWS($A$2:A2), COUNTIF($A$2:$A$20, “<=”&$A$2:$A$20), 0)), “”)

Where A2 through A20 represents the initial (unsorted) dates, along with a few blank cells for potential future inputs.

The formula is entered in a blank cell next to the column containing the original dates (C2 in our example), and it is finished by pressing the Ctrl, Shift, and Enter keys simultaneously. the formula down to the remaining cells after that (C2:C20 in our case).

Note: Make sure there are enough empty cells in the referred range so that freshly entered dates are automatically sorted. For instance, even if our list of dates falls inside the range A2:A7, we enter $A$2:$A$20 into the formula and fill cells C2 through C20 with it. By delivering an empty string (“”) instead of errors, the IFERROR function eliminates errors in the additional cells.

Scenario Where Customer Success Professionals Can Use This Excel Date Sorting Method:

There are times when customer success professionals have to dig out lot of information about the clients in a go. This is where this excel date sorting method can be very helpful.

Tips to remember if Excel does not accurately sort dates 

Make sure you submit your data in a format that Excel can understand if it does not seem to be sorting your data appropriately. You might have the numbers recorded as text if they are all left justified in their individual cells. The format can be fixed by choosing a date format from the Text drop-down box on the Excel ribbon.

Points to Ponder 

• Make the field format in General format when using the DAY, MONTH, and YEAR functions. 
• Cross check twice to verify that your dates are formatted appropriately. 

Frequently Asked Questions

Q 1: How can I keep rows together while sorting by date in Excel?

A 1: Follow the steps given below to keep rows together while sorting by date in Excel:

• Click Data > Sort after selecting the column of data you wish to sort.
• Keep the Expand the selection option checked in the Sort Warning dialogue and click Sort.
• Choose the criteria for sorting and the sorting order in the Sort dialogue box.
• Select OK.

Q 2: In Excel, how do I sort by the date, mm/dd/yyyy?

A 2: To choose the dates you want to sort by, drag down the column. Click the Oldest to Newest or Newest to Oldest buttons under the Sort & Filter section of the Home tab. 

Q 3: How do I sort sheets by date?

A 3: Simply select Data > Sort Range > Advanced Range. When your dataset is highlighted in Google Sheets, select Sorting Options from the menu bar. In the newly opened window, turn on “Data Has Header Row.” The “Sort By” drop-down menu will allow you to choose your date column. Choose “A > Z” from the menu to order your dates in ascending order.

Q 4: How can I make Excel’s date filter work again?

A 4: Select File from Excel. Select Options (usually in the bottom left corner of the screen). Open the Options window’s left pane and select the Advanced tab. Group dates in the AutoFilter menu should be checked in the workbook settings.

Q 5: How do I use Excel to filter data by date range?

A 5: On the Row Labels heading, click the drop-down arrow. From the Row Labels drop-down list, choose the field name. After selecting Date Filters, choose Between. Dates can be entered manually or chosen from pop-up calendars in the Between dialogue box.

Q 6: What makes a filter different from an advanced filter?

A 6: Data can be filtered using up to two criteria with AutoFilter, which are entered directly in the Custom AutoFilter dialogue box. The advanced criteria must be entered in a separate range on your worksheet to use Advanced Filter to find rows that satisfy multiple criteria in various columns.

Wrapping Up 

Our Excel date sorting narrative ends here. These methods should enable you to finish sorting exercises quite quickly. As you put these strategies into practice, we will put together another helpful Excel tutorial to help you come one step closer to excel ninja status.

It is simple and a good method to organize your data to sort it by date. You can complete it quickly if you use one of these approaches. Sorting data by date also helps to keep your work neat and organized.

---