Sorting implies rearranging data for getting a better visual presentation of your dataset. It is such a powerful tool that can organize text, numbers, and dates.
In this tutorial, I’ll show the ways to sort by date and time in Excel smartly including real-life examples.
Read more: How to Sort by Date in Excel
Download Practice Workbook
Excel Sort By Date And Time
Let’s have a look at our following dataset. Here, the order ID of products is given along with their delivery date, delivery time, and also price.
Now we’ll sort the above dataset based on delivery date and time.
Let’s get started.
1. Sort by Date and Time Using Direct Drop-down Option
Using the direct drop-down option, you can simply sort the dataset by date and time separately. Actually, this method doesn’t provide sorted data by date and time combinedly. But we have to know, what’s the problem with it!
For this just follow the following steps.
Firstly, select the whole dataset, then click Home tab>Sort & Filter toolbar>Filter option.
Alternatively, you can press the effective shortcut CTRL+SHIFT+L.
You’ll get the drop-down arrow for each heading of the dataset.
As you want to sort the dates, click on the drop-down arrow of the “delivery date” heading.
Then choose the Sort Oldest to Newest if you need to sort the dataset chronologically. Finally, press OK.
Similarly, click on the heading of the “delivery time” field. And, choose Sort Smallest to Largest if you wish to arrange time from start to endpoint.
After that, you’ll get the following sorted dataset.
The above screenshot clearly reveals that the order id is sorted based on time and the date is ignored here. How can we solve the problem? You’ll be introduced to a simple but powerful sorting option.
2. Sort by Date and Time Using Custom Sort Option
Custom sort is a special feature in Excel where you can specify different headings as levels and it provides the result combinedly based on the inserted levels.
Let’s see the application of the essential option in the case of our dataset.
Firstly, click on the Home tab>Sort & Filter toolbar>Custom Sort.
Then a dialog box will be opened.
Firstly, select the “delivery date” from the heading in the Sort by option.
Secondly, choose the Oldest to Newest as the Order.
As we want to sort time also, we have to append the desired heading. For this, click on the +Add Level box. Later, specify the “delivery time” as heading and Smallest to Largest as Order.
Afterward, you’ll get the following sorted dataset based on date and time.
3. Convert Date-Time to Number and Sorting
Assuming that, the delivery date and time are provided together simultaneously. Interestingly, you can do that.
Just insert the following formula in the Excel formula bar and press Enter.
Here, C5 is the delivery date and D5 is the delivery time.
You may be surprised how it is possible to add dates and time concurrently.
But it is really a simple thing, Excel counts the date as serial number and time as a fraction of the serial number.
Whatever, you’ll get the output as another format, later press CTRL+SHIFT+A for opening format cells and specify the number as your desired format.
Now you’ll get the following output.
As we want to sort the delivery date-time data, we have to convert the data into serial numbers.
For this insert the following formula.
Here, E5 is the delivery date-time as a custom format. Use the Fill Handle Tool to use the formula again for other cell values.
Now, it is time to sort the dataset based on the converted date-time data. Select the “Conversion of Date-time” as heading and Smallest to Largest as Order.
Finally, you’ll get the following output.
4. Using the MID & SEARCH Function
If you guys need to sort the dataset from the given Delivery day-date-time data, what can you do?
Firstly, we have to lessen the name of the day from the data. And, we may insert the combination of MID and SEARCH formula for doing that.
The MID function returns the middle number from a given text string. The syntax of the function is
=MID (text, start_num, num_chars)
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.
Besides, the SEARCH returns the position of the first character of find_text inside within_text
=SEARCH (find_text, within_text, [start_num])
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.
Now apply the following formula.
Here, C5 is the delivery day-date-time.
Later, convert the date-time data into value and sort using the Custom Sort option likewise the way discussed in the 3rd method.
Finally, you’ll get the following output.
Things to Keep in Mind
- Don’t forget that Excel stores dates as serial numbers. If you get your desired output as a serial number, just change the format using the Format Cells option.
- Also, be careful about the sorted data whether the whole dataset is changed or not.
This is how you can sort the dataset by date and time in Excel. If you have any suggestions or confusion, please let me know in the following comments section.
Thanks for being with me.
- How to Sort Data by Color in Excel (4 Criteria)
- How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
- Auto Sort When Data is Entered in Excel (3 Methods)
- How to Use Advanced Sorting Options in Excel
- How to Sort Multiple Columns in Excel (5 Quick Approaches)
- How to Sort Columns in Excel without Mixing Data
- Sum Using OFFSET and MATCH in Excel (With Alternative Options)