The option to sort your data by date is one of Excel’s most useful tools. Perhaps you need to arrange your family members’ birth dates in order to send out cards, organize your weekly budget transactions at the end of the year, or track sales earnings by month.
In this post, we will look at various methods for sorting dates in Excel. You’ll discover how to easily arrange dates in chronological order, sort by month while disregarding years, sort birthdays by month and day, and auto-sort by date while adding new data.
There are various methods for organizing your entries by date. Data may be sorted in ascending or descending order, and by day, week, month, or year. We’ll guide you through all of your options down below.
How to Sort Dates in a Sequential manner (Ascending or descending order)
You can adjust dates in excel in an easy manner by using the standard ascending options. follow the steps below to do this;
- Choose the dates to be sorted accordingly.
- Click Sort & Filter in the Editing group on the Home tab, then Sort Oldest to Newest. You may also utilize the A-Z option on the Data tab, under the Sort & Filter group.
How to sort by date in Excel
The Excel sort options may also be used to rearrange the entire table, rather than just a single column. The trick to sorting data by date while keeping the rows intact is to widen the selection when requested.
The following are the full instructions for sorting data in Excel by date:
- Select the dates without the column headings in your spreadsheet.
- Click Sort & Filter on the Home page and select Sort Oldest to Newest.
The Sort Warning dialog box will be shown. Leave the Expand the selection option selected by default, and then click Sort
The dates have been sorted from the oldest to the newest.
You may also get the same effect by choosing your data and right-clicking to select Sort. Then, decide on the best arrangement for your data.
This is useful when sorting a simple list of dates and intending to insert the related data later. Sorting in this manner will always organize your dates chronologically, first with the year, then the month, and finally the day.
This is a very simple way of sorting out your records in a chronological manner.
Sort by date using Excel’s custom function
Another useful method for sorting by date is Custom Sort. If you have headers in your data, you can utilize them as categories to conveniently sort items, even if they aren’t in the first column.
In the example below, a basic sort, as seen before, would sort the entries in the Transaction column alphabetically, rather than placing your dates in order. We need to use a Custom Sort to instruct Excel to sort the dates in Column B because the date is in the second column.
To accomplish this, first:
Highlight the headers and the information beneath them. Click Home > Sort & Filter > Custom Sort on the Excel ribbon. Choose Date from the dropdown menu next to Sort by.
To utilize your data headers as sorting categories, check the box in the top-right corner. From the Order dropdown option, choose Oldest to Newest or Newest to Oldest.
Sorting by month in excel
There may be occasions when you want to organize dates by month rather than year, such as when gathering anniversary dates of coworkers or relatives. The usual Excel sort feature will not work in this scenario because it always considers the year, even if your cells are set to display simply the month or the month and day.
The answer is to insert a helper column, extract the month number, and sort by it. Use the MONTH function to retrieve a month from a date.
In the example below, we use the following method to extract the month number from the date in B2:
Sort your table now by the Month column.
- Select the month digits (B2:B12), then
- click Sort & Filter
- Sort Smallest to Largest, and then extend the selection when Excel prompts you to.
If everything is done correctly, you should obtain the following result:
Sorting dates by Years in excel
The simplest approach to sorting by year is to arrange dates in chronological order using Excel’s ascending sort (Oldest to Newest) tool.
As illustrated in the picture below, this will sort dates by year, month, and day.
You can also use this formula =YEAR() to extract the year from the date
How to sort date by weekday in excel
You will also require a helper column, as shown in the previous instances, to sort data by weekday. In this scenario, we will sort by the aid column after filling it with the WEEKDAY formula, which produces a number matching to the day of the week.
This is the formula to apply for a week that runs from Sunday (1) through Saturday (7):
In certain instances, the week starts from Monday (1) to Sunday (7). In that case, you use the formula below
Where A2 is the cell containing the date
Sorting date by month and day in excel
When organizing dates for a birthday calendar, sorting dates by month and day is the best method. As a result, you’ll need the formula to extract months and days from birth dates.
The Excel TEXT function, which may convert a date to a text string in the desired format, comes in helpful in this scenario. The “mmdd” or “mm.dd” format code will suffice for our needs.
With the source date in B2, the formula looks like this:
TEXT = (A2, “mm.dd”)
Then, sort the Month and Day columns from largest to smallest, and the data will be organized in the order of the days of each month.
Sorting date in excel by month and weekday names
If you have a list of month names as text rather than dates structured to display just months, using Excel’s ascending sort may be an issue since it will organize the month names alphabetically rather than sorting by month order from January to December. In this scenario, a custom sort will come in handy:
- Specify the entries to be sorted by month name.
- Sort may be found on the Data tab, under the Sort & Filter group.
Perform the following in the Sort dialog box:
- Find the list of the column containing the month names under Column.
- Select Cell Values from the Sort on the dropdown menu.
- Select Custom List from the Order dropdown menu.
Select either full month names (January, February, March,..) or abbreviated names (Jan, Feb, Mar…) in the Custom Lists dialog box, depending on how the months are displayed in your worksheet:
Click on OK twice