How to sort by date in Excel: in chronological order, by month, auto sort

In this article, we will look at different ways to sort dates in Excel. You will learn how to quickly arrange dates in chronological order, sort by month ignoring years, sort birthdays by month and day, and how to auto sort by date when entering new values.

Excel's built-in sort options are powerful and effective tools, but they do not always work correctly when it comes to sorting dates. This tutorial will teach you a few useful tricks to arrange Excel by date in a meaningful way without messing up your data.

How to sort dates in chronological order

Arranging dates in chronological order in Excel is very easy. You just use the standard Ascending Sort option:

  1. Select the dates you want to sort chronologically.
  2. On the Home tab, in the Formats group, click Sort & Filter and select Sort Oldest to Newest. Alternatively, you can use the A-Z option on the Data tab, in the Sort & Filter group.
    Sorting dates in chronological order in Excel

How to sort by date in Excel

The Excel sort options can also be used for re-arranging the whole table, not just a single column. To sort records by date keeping the rows intact, the key point is to expand the selection when prompted.

Here are the detailed steps sort data in Excel by date wise:

  1. In your spreadsheet, select the dates without the column header.
  2. On the Home tab, click Sort & Filter and choose Sort Oldest to Newest.
    Sorting by date in Excel
  3. The Sort Warning dialog box will appear. Leave the default Expand the selection option selected, and click Sort:
    Choose to expand the selection.

That's it! The records have been sorted by date and all the rows are kept together:
The records have been sorted by date keeping the rows intact.

How to sort by month in Excel

There may be times when you wish to sort dates by month ignoring the year, for example when grouping anniversary dates of your colleagues or relatives. In this case, the default Excel sort feature won't work because it always considers the year, even if your cells are formatted to display only the month or month and day.

The solution is to add a helper column, extract the month number and sort by that column. To get a month from date, use the MONTH function.

In the screenshot below, we extract the month number from the date in B2 with this formula:

=MONTH(B2)
Extract the month number to sort data by month.

Tip. If the result is displayed as date rather than a number, set the General format to the formula cells.

And now, sort your table by the Month column. For this, select the month numbers (C2:C8), click Sort & Filter > Sort Smallest to Largest, and then expand the selection when Excel asks you to do so. If all done correctly, you will get the following result:
The data is sorted by month ignoring days and years.

Please pay attention that our data is now sorted by month, ignoring the years and days within each month. If you'd like to sort by month and day, then follow the instructions from the next example.

If the month names are entered as text, then sort by a custom list as explained in this example.

How to sort birthdays in Excel by month and day

When arranging dates for a birthday calendar, the optimal solution will be sorting dates by month and day. Consequently, you need a formula that would pull months and days from the dates of birth.

In this case, the Excel TEXT function, which can convert a date to a text string in the specified format, comes in handy. For our purpose, the "mmdd" or "mm.dd" format code will work.

With the source date in B2, the formula takes this form:

=TEXT(B2, "mm.dd")

Next, sort the Month and day column from largest to smallest, and you will have the data arranged in order of the days of each month.
Sorting birthdays by month and day

The same result can be achieved by using the DATE formula like this:

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

The formula generates a list of dates by extracting the month and day from the actual date in B2 and replacing the real year with a fake one, 2000 in this example, though you can put any. The idea is to have the same year for all the dates, and then sort the list of dates in chronological order. Since the year is the same, the dates will be sorted by month and day, which is exactly what you are looking for.

How to sort data by year in Excel

When it comes to sorting by year, the easiest way is to arrange dates in chronological order with Excel's ascending sort (Oldest to Newest) option.
Sorting data by date in Excel

This will sort dates by year, then by month, and then by day as shown in the screenshot below.
The dates are sorted by year, then by month, and then by day

If for some reason you are not happy with such an arrangement, then you can add a helper column with the YEAR formula that extracts the year from the date:

=YEAR(C2)

After sorting data by the Year column, you will notice that the dates are sorted by year only, ignoring months and days.
Sorting data by year, ignoring months and days

Tip. If you'd like to sort dates by day disregarding months and years, extract the day by using the DAY function, and then sort by the Day column:

=DAY(B2)

How to sort by days of the week in Excel

To sort data by weekday, you will also need a helper column like in the previous examples. In this case, we will be populating the helper column with the WEEKDAY formula that returns a number corresponding to the day of the week, and then sorting by the helper column.

For a week that starts from Sunday (1) to Saturday (7), this is the formula to use:

=WEEKDAY(A2)

If your week starts from Monday (1) to Sunday (7), here's is the right one:

=WEEKDAY(A2, 2)

Where A2 is the cell containing the date.

For this example, we used the first formula and got this result:
Sorting by days of the week in Excel

In case the weekday names are entered as text, not as dates, then use the Custom Sort feature as explained in the next example.

How to sort data in Excel by month names (or weekday names)

In case you have a list of month names as text, not as dates formatted to display only months, applying Excel's ascending sort might be a problem - it will arrange the months names alphabetically instead of sorting by month order from January to December. In this case, a custom sort will help:

  1. Select the records that you want to sort by month name.
  2. On the Data tab, in the Sort & Filter group, click Sort.
  3. In the Sort dialog box, do the following:
    • Under Column, select the name of the column that contains the month names.
    • Under Sort on, choose Cell Values.
    • Under Order, select Custom List.

      • Using custom sort in Excel

  4. In the Custom Lists dialog box, select either full month names (January, February, March, …) or short names (Jan, Feb, Mar…) depending on how the months are listed in your worksheet:
    Sorting by month name from January to December
  5. Click OK twice to close both dialog boxes.

Done! Your data has been sorted by month name in chronological order, not alphabetically:
Data is sorted by month name in chronological order.

Tip. To sort by the names of the days of the week, select either full names (Sunday, Monday, Tuesday, …) or short names (Sun, Mon, Tue…) in the Custom Lists dialog box.

How to auto sort by date in Excel

As you have seen, the Excel Sort feature copes with a variety of challenges. The only drawback is that it isn't dynamic. Meaning, you will have to re-sort your data with every change and whenever new information is added. Perhaps you are wondering if there is a way to sort automatically every time a new date is added so that your data is always in order.

The best way to accomplish this is by using a macro. Below, you will find a couple of code examples to auto sort the following data by date in chronological order.
Auto sort by date in Excel

Macro 1: Auto sort with every worksheet change

This macro is executed whenever a change occurs anywhere in the worksheet.

It is assumed that your data are in columns A through C, and the dates that you want to sort by are in column C, beginning in C2. It is also assumed that row 1 contains headers (Header:=xlYes). If your records are in different columns, then make the following adjustments:

  • Change the A1 reference to the upper left cell of your target range (including headers).
  • Change the C2 reference to the topmost cell containing a date.

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: Auto sort when changes are made to a specific range

If you are working with a huge worksheet that contains a lot of information, re-sorting with absolutely any change in the sheet may be troublesome. In this case, it makes sense to limit the triggering of the macro to the changes that occur in a specific range. The following VBA code sorts the data only when a change is made in column C that contains the dates.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C:C")) Is Nothing Then Range("A1").Sort Key1:=Range("C2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End Sub

Tip. These macros can be used to auto sort by any data type, not just dates. Our sample codes sort in ascending order. If you'd like to sort descending, change Order1:=xlAscending to Order1:=xlDescending.

How to add the macro to your worksheet

As both macros are run automatically on a worksheet's change, the code should be inserted in the sheet where you want to sort data (Sheet1 in this example). Here's how:

  1. Press Alt + F11 to open the VBA Editor.
  2. In the Project Explorer on the left, double click the sheet where you want to auto sort.
  3. Paste the code in the Code window.
    A macro to auto sort by date in Excel

Auto sort dates with formula

Supposing you have a list of dates and you'd like to have them automatically arranged in chronological order in a separate column, side-by-side with the original list. This can be done with the following array formula:

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

Where A2:A20 are the original (unsorted) dates, including a few empty cells for possible new entries.

Enter the formula in a blank cell beside the column with the original dates (C2 in this example) and press the Ctrl + Shift + Enter keys simultaneously to complete it. Then, drag the formula down to the remaining cells (C2:C20 in our case).
Formula to auto sort dates in Excel

Tip. To have newly added dates sorted automatically, be sure to include a sufficient number of blank cells in the referred range. For instance, our list of dates is in the range A2:A7, but we supply $A$2:$A$20 to the formula, and populate it into the cells C2 through C20. The IFERROR function prevents errors in the extra cells, returning an empty string ("") instead.

Excel sort by date not working

If your dates are not sorted as they should, most likely they are entered in a format that Excel cannot understand, therefore they are perceived as text strings rather than dates. The following tutorial explains how to distinguish the so-called "text dates" and convert them to normal Excel dates: How to convert text to date in Excel.

That's how to sort by date in Excel. I thank you for reading and hope to see you on our blog next week!

Available downloads

Sort by date formula examples (.xlsx file)
Auto sort macro (.xlsm file)

Table of contents

42 comments

  1. I have a table of names, with dates in the next column over. Id like to have the names automatically sort in chronological order, when a new date is entered.

    The total picture is a auto sorting date of people working overtime with the oldest date on top, I want to than have a column where I can click, Not working, and the rows where that is selected, populate in another table. This would show available names to work a current overtime shift. Thank you

  2. The VBA Code worked well. But there's a problem when you entered date mistakenly and it get sorted automatically. For the large set of data with entries of around 2000 you are not able to perform UNDO action.

    IS there any solution for this. Please Suggest.!

    The Code i've used:

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next

    If Not Intersect(Target, Range("C:C")) Is Nothing Then
    Range("A1").Sort Key1:=Range("C2"), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    End If

    End Sub

  3. The array formula works great for our needs but, like James Peters stated in comment #7, if there are two dates that are the same, it leaves a blank cell. Is there any possible workaround for this? I've tried messing with the formula, not making it an array formula, etc. and nothing seems to be working. Thank you for anything you can help with!

      • hi..just wanted to sort my data , from the dates between 13th - 31st of each month only…

        do you have any formula for that..

        thank you in advance

        • Hello!
          Get the day from a date into a separate column. To do this, use a condition with the DAY function.

          =IF(DAY(A1) < 13,"",DAY(A1))

          Then sort by that column as described in this article above.
          You can also sort by formula using the SORTBY function:

          =SORTBY(A1:A8,(DAY(A1:A8) > 13)*DAY(A1:A8),1)

          I hope this will help.

  4. Is it possible to sort , "From date and time" to "To date and time ".

    Thanks

  5. I have an excel (huge date) 8000 + rows
    i have sorted the data dateA to Z wise, but i also want to sort the date wise keeping the alphabetical list intact

    IS IT POSSIBLE ???

  6. i have 500 row data of 5 column
    a,b,c,d,e,f
    number,date,company name,trade, value

    all the date are in ascending position, how can change the date in descending position but keep the same company serial wise.

  7. Hi,

    Thank you for all of this. I have dated items (date/month/year) but for some items i do not have a date, only the month and year. Is there a way to get excel to recognise that ?/5/21 is a date in May 2021 and sort these entries accordingly? As it is, all entries with no specific date are moved to the end of the spreadsheet. Many thanks if you can assist.

    Raymond

  8. I have a spread sheet to keep track of when patients are either due for an appointment or scheduled so I have two columns of dates but data will only be in one column because they either need an appointment (column D) or have an appointment scheduled (column E) is there a way to auto sort the dates when info is deleted from one column and entered into the other i.e. a date is deleted from column D and a new date is entered into column E or vice versa when the date is deleted from column E and a new due date is entered in column D? I would also like Column D to have the conditional formatting color scale of Green Yellow Red where Red are dates that are either past due or due soon. Is this at all possible?

  9. Sir,I want sort data by religious(christian, muslim,hindu etc),sub religious(if christian -Roman catholic,Latin catholic etc), from the excel data

  10. I tested this formula in a worksheet, and it gets me 99% of the way, but I noticed if I have a duplicated date in the referenced column, it does not display the date in the new column - only creates a blank row and then moves to the next day. Is there a workaround for this?

  11. super helpful tips, thanks

  12. Mmmh. Mine asks to expand selection before offering to order by date. And then only offers to order A-Z not by date.

  13. Hi Alexander,
    How can I auto sort multiple data fields on one spreadsheet.

    For Example using example given. if you split the citrus fruit and the other fruit into to separate data fields. Hope that makes sense.

    I believe that is what Matt is asking for above, post 6.

    Cameron

  14. I have been searching for hours to find a way of writing dates so that they can be sorted.
    In spite of reading everything with great care nothing seems to work in the way that it should.
    Even the simple things, if I write a four figure no. and irrespective of which format I use, Excel accepts that no. as either 2units + 2 decimal places or the decimalised figure rounded up to the next whole no.
    For example 1785 would be shown as 17.85 or 18.
    What am I not doing ?

    • Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? 1785 is a number, not a date. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  15. Is there a way to make the same code but with multiple sort layers?

  16. Need help posting a formula that will automatically sort by date after after new entries are made.

    0000-00 Smith John Doe 1/1/87 Monday, October 22, 2001 LV

    I have 7 columns and I would like it sorted by date or column 6.

    Can anyone advise how to accomplish this?

  17. NOTHING TO UNDERSTAND

  18. Sort data into student quiz and arrange marks in descending order one example

  19. Respected Sir i want short by subject of the student for examples
    Sr. No. Name Student ID Subject
    1. Ram BA20201 Hindi, English, History,
    2. Shyam BA20202 Sanskrit, Political Science, History
    3. Mohan BSC20192 Music, Drama, History
    4. Sohan BCom Hindi, English, Accounting
    now i want to know how many student has taken Hindi and with hindi which subject taken by all the student those who have take hindi

  20. thanx,do u provide hep in all office tools?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)