How to Sort in Excel by Date (3 Ways)

Microsoft Excel is full of amazing built-in tools to help you organize your worksheets. One of the powerful functions of Excel is to quickly sort the dates chronologically. When a data-sheet is lengthy, you may need to sort the dates which will be very time-saving. This article will show you how you will sort the data in Excel by dates in 3 simple ways and every other thing you need to know about date insertion in Microsoft Excel sheets.

Quick Solution

To sort in Excel by Date,

  • Select the column that contains the dates.
  • Go to the Data
  • Select A-Z or Z-A
  • Click on Expand the Selection from the Sort Warning dialog box.
  • Then hit Sort.

A quick solution to sort in Excel by date

🔗How to Sort Multiple Columns in Excel (4 Techniques)

Technique #1: Use the A-Z or Z-A Button to Sort in Excel by Date

❶ Select the column that is carrying the dates by clicking on it. I have selected column D.

Select the column that is carrying the dates by clicking on it

❷ Go to the Data tab.

❸ Now click on the A-Z or Z-A command from the Sort & Filter group.

Use the A-Z or Z-A Button to Sort in Excel by Date

A Sort Warning dialog box will show up to ask your permission.

❹ Choose the Expand the Selection option to continue the process.

❺ Finally click on Sort.

🔗 How to Sort in Excel by Name (3 Techniques)

Technique #2: Use the Filter Command to Sort in Excel by Date

Follow the following steps to sort dates in Excel by using the Filter command,

❶ Select the entire data set.

❷ Then click on the Home tab.

❸ Now under the Editing group, click on the Sort & Filter drop-down list and select Filter. Or you can use the shortcut CTRL + SHIFT + L.

Use the Filter Command to Sort in Excel by Date

 You will see small drop-down arrows will appear for each heading.

❹ Then click on the small arrow beside the date column header.

❺ Now hit Sort Oldest to Newest or Sort Newest to Oldest option.

Hit Sort Oldest to Newest or Sort Newest to Oldest option

I have selected the Sort Oldest to Newest option for my data sheet and here is the resulting date arrangement in the image below:

🔗 How to Sort by Number in Excel (4 Techniques)

Technique #3: Use the Custom Sort Command to Sort in Excel by Date

❶ First go to the Home tab.

❷ Then click on the Sort & Filter drop-down list (under the Editing group).

❸ Now click on the Custom Sort command.

Use the Custom Sort Command to Sort in Excel by Date

You will see that the Sort Dialog Box will come along.

❹  From the Sort by drop-down, select the date-related column header among the headers.

As for me, the date-related column header is the Delivery Date.

❺ Then change the Order. Make it Oldest to Newest or Newest to Oldest as per your choice.

❻ Finally press OK.


Note

Put the cursor on any cell before clicking on the Custom Sort command or else the Sort Dialog Box won’t appear. Otherwise, a pop-up like this one will show up:


🔗 How to Sort Data in Excel Using a Formula (7 Formulas)

How to Insert Today’s Date in Excel

To insert today’s date in an Excel sheet, I use the following steps:

❶ Double-click on the cell where you want to put today’s date.

❷ Type the following formula of the TODAY function.

=TODAY()

How to Insert Today’s Date in Excel

❸ Press ENTER.

Today is March 15th, 2022. The date appeared on my sheet as it is.

🔗 How to Sort in Excel by Color (3 Techniques)

How to Combine Date & Time into One Cell in Excel

Sometimes we need to insert the date and time into one single cell to keep our datasheet more managed. To insert the date and time into one cell follow the following steps:

❶ Create a separate column where you want to get the time and date together.

I made a separate column named Entry Details in column H.

❷ Now insert the following formula in the first cell of the new column.

I have inserted the following formula of the SUM function inside the H2 cell.

=SUM(D2,E2)

Usage of the SUM function to Combine Date & Time into One Cell in Excel


Formula Explanation

  • SUM ➤ is responsible for combining the columns.
  • D2, E2 ➤ is the column of date and time respectively.

❸ Now press the ENTER button to perform the above formula.

❹ Then hold the cursor on the Fill Handle and drag it from cell H2 to H10. Done!

Combine Date & Time into One Cell in Excel

Case #1: Date and Time not Appearing Together

Sometimes, you may find a situation where applying this formula won’t show the date and time together in a cell, rather only the date will appear in the new column.

This problem happens when the cell format is wrong.

To solve this issue,

❶ Go to the Format drop-down under the Home tab and select the Format Cells command.

The Format Cells Dialog Box will come along.

❷ Under the Custom category, type the date and time format which you have chosen in the empty box.

I have the dates in m/dd/yyyy format and the time in h:mm format.

❸ Hit OK to apply the change.

Format Cells dialog box

Case #2: Formula Return #### Error

Sometimes the cell will display characters like ##### as the result.

This problem occurs when the cell is not wide enough to fit in all the cell contents. Formulas that result in negative date and time will also display #####, as Excel does not support negative date and time.

To solve the ##### problem,

Double click on the column header or drag its width to the right side as per your preference.

Sort Month in Excel DD/MM/YYYY

Sorting months from the dates in Excel are very useful in many cases. For instance, you may need to sort the months from the dates for delivering your upcoming project results or just simply remember your colleagues’ birthdays so as to have a birthday party.

To sort the months in Excel from dates,

❶ Create a new column for months.

I named the column header Month.

❷ Select the first empty cell under that column.

Insert the following formula of the MONTH function:

=MONTH(A2)

Sort Month in Excel DD/MM/YYYY


Formula Explanation

  • Month ➤ it identifies the month.
  • A2 ➤ It is the first entry of the Date column.

❸ Press Enter.

❹ Then drag the Fill Handle till the end of the column (B2-B10).

The end result will be like the image below:

Sort Year in Excel DD/MM/YYYY

Instead of months, you can sort only the years from the dates. Sorting the years can be very functional while drawing up a graph of several years of changing GDP or changing the annual average temperature of a country.

To sort years in Excel perform these steps,

❶ Add a new column for years.

I titled the column header Year.

❷ Select the first empty cell under that column.

Insert the following formula of the YEAR function:

=YEAR(A2)

Sort Year in Excel DD/MM/YYYY


Formula Explanation

  • Year ➤ it defines the years.
  • A2 ➤ It is the first entry of the Date column.

❸ Press Enter.

❹ Then press and drag the Fill Handle till the end of the column (B2-B10).

And you are done! Here is the end result:

How to Sort Dates in Excel Based on Month and Day

We often need to sort dates where the year is irrelevant to add. Excel allows you to sort the dates based on only month and day, not adding the year. You can sort the dates of your forthcoming meetings this way since mentioning the year is unnecessary here.

To sort month and day,

❶ Insert a new column on the excel sheet and give it a header.

I named it Day and Month.

❷ Then insert the following formula of TEXT function in the first cell of the new column:

=TEXT(B2,"DD-MMM")

How to Sort Dates in Excel Based on Month and Day


Formula Explanation

  • TEXT ➤  It allows the name of the month to convert into text form.
  • B2 ➤  It is the first entry of the Date column.
  • DD ➤ It represents the day.
  • MMM ➤ It represents the first 3 characters of the month in text form.

❸ Press Enter to apply the formula.

❹ Now drag the Fill Handle through the end of the column (C2-C10).

Now you can sort the dates chronologically using the Sort command following Technique #1.

Sort by Date in Excel Not Working

If the sorting techniques do not work in your datasheet, don’t panic! It is probably a formatting issue and it’s very easy to solve. Your dates are most probably formatted in text strings that Excel can not read as dates. So you need to change the format to normal Excel dates.

To solve this problem,

❶ Select the column of dates and go to the Home tab.

❷ Then click on the Drop-down menu (under the Number group).

❸ Select Short Date or Long Date as per your preference.

Sort by Date in Excel Not Working

Conclusion

I hope these above techniques to sort the dates would be enough for you to solve any kind of date sorting problems. Now you can easily sort the dates in different chronological commands, whichever you like! If you find something confusing, feel free to let us know in the comment section.

(Visited 570 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *