How to Sort by Date and Time in Excel (4 Useful Methods)

Let’s have a look at our sample dataset. Here, the Order ID of products is given along with their Delivery Date, Delivery Time, and Price. We’ll sort the dataset based on Delivery Date and Time.

Method 1 – Using the Direct Excel Drop-down Option to Sort Date and Time

• Select the whole dataset.
• Click on the Home tab, go to the Sort & Filter toolbar, and select Filter
• You can also use Ctrl + Shift+ L.

• You’ll get the drop-down arrow for each heading of the dataset like this.

• To sort the dates, click on the drop-down arrow of the Delivery Date
• Choose the Sort Oldest to Newest if you need to sort the dataset chronologically.
• Press OK.

• The table will be sorted based on the Delivery Date cells chronologically.

• Click on the heading of the Delivery Time and choose Sort Smallest to Largest.

• You’ll get the following sorted dataset.

• This overwrites the first sort, so you can sort either by date or by time.

Read More: How to Sort Dates in Chronological Order in Excel

Method 2 – Applying the Custom Sort Option to Sort Date and Time in Excel

• Select the dataset.
• Click on the Home tab and go to the Sort & Filter toolbar, then select Custom Sort.

• A Sort dialog box will appear.
• Select the Delivery Date from the heading in the Sort by box.
• Choose the Oldest to Newest in the Order.

• Click on the +Add Level.
• Specify the Delivery Time as the heading, and Smallest to Largest as Order.
• Click OK.

• Here’s the output for our sample dataset.

Method 3 – Converting Date-Time to Numbers and Sorting in Excel

Let’s get the Delivery Date and Time provided together simultaneously. We can sort based on both.

• Insert the following formula in the E5 cell and press Enter.

C5 is the Delivery Date and D5 is the Delivery Time.

• Press Enter.
• Use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the E5 cell.

• We’ll get the outputs like this.

• Insert the following formula in the F5 cell like this.

• Press Enter and use the Fill Handle.

• Select the dataset.
• Go to Home and click Editing.
• Choose Sort & Filter and select Custom Sort.

• If you don’t select the whole dataset, this warning will appear. Select Expand the selection and click Sort.

• The Sort window will appear.
• Choose Sorted Delivery Date-Time in the Sort by box and Smallest to Largest in the Order box.
• Click OK.

• We’ll get the outputs in Column F in a different format. Right-click the data of Column F and select Format Cells.

• A Format Cells window will appear.
• Go to Number, select Custom, and choose m/d/yyyy h:mm in the Type field.
• Click OK.

• We’ll see the output like this.

Read More: How to Sort by Date in Excel

Method 4 – Applying Excel MID and SEARCH Functions to Sort by Time and Date

The MID function returns the middle number from a given text string. The syntax of the function is.

The arguments are-
text – The text to extract from.
start_num – The location of the first character to extract.
num_chars – The number of characters to extract.

The SEARCH returns the position of the first character of find_text inside within_text.

The arguments are
find_text – The text to find.
within_text – The text to search within.
start_num – [optional] Starting position in the text to search. Optional, defaults to 1.

• Apply the following formula in D5:

Here, C5 is the Delivery Day-Date-Time.

• Press Enter.
• Use the Fill Handle.

• Use this formula in the E5 cell to change Delivery Date-Time into a number using the VALUE function in order to sort them later.

• Press Enter and use the Fill Handle.

• To sort the values chronologically, copy cells E5:E16 and paste those to F5.

• Select the cells and Sort them and then use the Format Cells option to give the specific format.
• You’ll get the output like this.

Read More: How to Sort Dates in Excel by Month and Year

Things to Remember

• Excel stores date as serial numbers. If you get your desired output as a serial number, change the format using the Format Cells option.
• Be careful about the sorted data whether the whole dataset is changed or not.

Download the Practice Workbook

<< Go Back to Sort by Date in Excel | Sort in Excel | Learn Excel