Open In App

How to Sort by Date in Excel?

Last Updated: 27 May, 2024

N

Naveen Kumar Kharwal
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

Excel provides various types of sorting formats e.g. “sort A to Z”, “Sort Z to A”, “Sort by color” and “Sort by date” etc. Sorting in Excel is nothing but arranging data into some meaningful order to make it easier to understand, analyze, or visualize.

Here, we will be discussing how to sort by Date in Excel whether it may be Years, Months, Days, or weeks.

how-to-Sort-by-Date-in-excel-copy

How to Sort by Date in Excel?

How to Sort by Date in Excel?

Chronological or reverse chronological order is the easiest way to sort data by date. This sorts the data in ascending or descending order. In Excel, you must have the dates in one single format to sort the data by date otherwise they do not always work.

To sort the dates in chronological order follow the below steps:

Step 1:  Select the dates which you want to sort in ascending or descending order.

Dataset

Step 2: Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Oldest to Newest.

Sort Oldest to Newest

As a result, data is sorted from oldest to newest.

Step 3:  If you want to sort dates from Newest to Oldest, then Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Newest to Oldest.

Sort Newest to Oldest

As a result, data is sorted by newest to oldest.

How to Sort Data by Month in Excel

If you want to sort dates by month, ignoring the year, then you have to add a helper column, extract the month number, and sort by that column because the default Excel sort feature always considers the year.

You can use the “MONTH” function to get a month from the date.

Syntax:

” =MONTH (serial_number) “

Now follow the below steps to sort by month in Excel:

Step 1: Extract the month number from the date column.

Extract the month number from the date column

In the above example, the month number is extracted from the “date of birth” column by using the ” =MONTH(D3) “ formula:

Step 2: Sort the table by the Month column, select the month range which you want to sort. Then Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Smallest to Largest.

Sort Smallest to Largest

As a result, data is sorted by month smallest to largest.

How to Sort Data by Month and Day in Excel

Excel TEXT functions are assisting for it. It converts a date into a text string in the specified format. For our function, the “mm-dd” format code will work.

Syntax:

” =Text(Value, format_text) “

Now follow the below steps to sort the by Month and day in Excel: 

Step 1: Extract the month and day from the date column.

Using TEXT function

In the above example, the “month and date“ is extracted from the “date of birth“ column by using the =TEXT(D3,”mm-dd”) formula:

Step 2: Sort the table by the Month and Day column, select the Month and Day column range which you want to sort. Then Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Z to A.

Sort Z to A

As a result, data is now sorted by “month and day”, largest to smallest.

How to Sort Data by Year in Excel

When you want to sort data by year, the easiest way is to arrange dates in chronological order with Excel’s ascending sort (oldest to newest) option.

Sort Data by Year

Sort data by Year

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

Sorted Data

Sorted Data

How to Sort by Days of the Week in Excel

Embraking the journey of sorting data by days of the week in Excel opens up a realm of insightful organization. This method empowers you to rearrange information in a sequence that mirrors the flow of the week, a vital perspective for various analytic pursuits.

The path to weekday-based sorting demands the creation of a helper column, mirroring previous strategies. In this iteration, however, the helper column takes on the form of the WEEKDAY formula. This formula delivers a numerical representation corresponding to the day of the week.

For a Week from Sunday(1) to Saturday(7), the formula used is:

” =WEEKDAY(A2) “

Unleashing the Weekday Sorting

With the helper column meticulously curated, the act of sorting unfurls with precision. Each data point aligns itself with its respective day of the week, offering a snapshot of your information’s temporal rhythm.

As you go through the mechanics of weekday-based sorting, remember that Excel’s versatility thrives on these strategies. By unveiling patterns hidden within your data’s temporal context, you harness the power of organization to elevate.

Conclusion

In conclusion, mastering the process of sorting by date in Excel can significantly enhance your data analysis and organizational skills. By utilizing features like the Sort function, custom sort options, and even advanced techniques like creating helper columns, you can optimize your spreadsheets for better readability and more insightful data interpretation. This skill not only boosts your productivity but also ensures that your data is always presented in a clear and logical sequence.

How to Sort by Date in Excel? – FAQs

Why can’t I sort by date in Excel?

You might not be able to sort by date in Excel if the dates are formatted as text instead of date. Excel needs to recognize the data as a date to sort it correctly. Make sure your date cells are formatted as dates (you can check and change this in the format cells option).

How to filter Excel by date?

To filter by date in Excel:

  1. Click on the Data tab at the top.
  2. Ensure your date column has a header.
  3. Click “Filter” in the toolbar; this adds drop-down arrows in the header row.
  4. Click the drop-down arrow in your date column, and you’ll see date filtering options like “Before,” “After,” and specific date ranges you can select.

Why is Excel sorting dates from A to Z?

If Excel is sorting your dates from A to Z, it’s likely treating them as text rather than dates. To fix this, you should check the formatting of your date column and make sure it’s set to a date format. Once Excel recognizes the entries as dates, it will sort them chronologically instead of alphabetically.

How to sort a date in Excel with multiple columns?

To sort by date across multiple columns:

  • Select all the data you want to sort.
  • Go to the Data tab and click “Sort.”
  • In the Sort dialog, you can add levels (use “Add Level”) to sort by. Choose your main date column for the first level, and then add other columns as secondary criteria.
  • Make sure to set the correct order for each level (oldest to newest or newest to oldest) based on your needs.


Article Tags :

N

Naveen Kumar Kharwal
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

R

Romy Kumari

How to Calculate End Date from Start Date and Duration in Excel?

Excel is a powerful data visualization and analysis software, which uses spreadsheets for managing, storing, and visualizing a large amount of data. A cell, a rectangular block is used to store each unit of data. This tool can be used to visualize trends or compare data by creating graphs, or get insights from the data using functions and formulas. It is primarily used for accounting purposes but can be used by anyone for many different tasks as per the requirements. Let's see how to calculate the end date from the start date and duration. Calculate the end date from the start date and duration (weeks) To calculate the end date from the start date and the number of weeks(duration), use the below formula: =IF(start_date, start_date+(duration_in_weeks*7),"") This formula will add (week * 7) days to the start date, as a week has 7 days. For purpose of demonstration use the below data to calculate the end date: Step 1: Apply the formula, Step 2: Drag the fill handle over to the data range. Step 3: To change the value into date format, click the Home tab, and choose Short Date from the General drop-down list. Output Calculate the end date from the start date and duration (mont
Read More

S

Saurabh Gupta

How to Combine Text with Date or Time in Excel?

article_img
Microsoft Excel allows its users to store and manage different varieties of data in the form of rows and columns and store it inside an Excel workbook. Since Microsoft Excel allows its users to not only store data but also manipulate that data later on as per their needs. Combine Text with Date or Time in Excel Let's assume a scenario where a user is having data in two different columns. In the first column, the user has some text and in the second column, the user has either date or time values and the user wants to manipulate this data in such a way that he/she can combine the data of both of these columns in a third column. Text Date Final Form MS Dhoni was born on07/07/1981MS Dhoni was born on 07/07/1981Virat Kohli was born on 05/11/1988Virat Kohli was born on 05/11/1988 Now, we can observe that our goal is to append data of one column with data of another column. Microsoft Excel provides us with two useful ways using which we can combine these two elements. The first one is the Ampersand (&) symbol/operator and the second one is the CONCAT() function. Method 1: Combining text with date/time using Ampersand (&) operator We can use the & operator to combine two elem
Read More

A

Ayush Dey

VBA Date and Time Functions in Excel

article_img
Date and Time Functions are the inbuilt functions that give us the opportunity to see the date or time according to the user's need. Suppose a user needs to see the month or the day or the year then it can be easily seen by different date functions. Similarly, for the time function, also we can manipulate it according to the need of the user. Date and Time functions are used to interconvert date and time in different formats. In this article, we will learn about the most commonly used date and time functions. VBA Date Functions There are fifteen-plus different date functions in VBA, but here we will talk about some of the most commonly used date functions. VBA Date Function The Date() function returns the current date. The Date() function does not require any arguments. For example, declare a variable name date_1 of Date data type, call the Date() function, and store the return value in date_1, then print the date_1 in the console. Syntax of the function: Date() VBA DateAdd Function The DateAdd() function is used to add an interval of date/time to the respective date or time. The function will return the resulting date or time. The function takes three arguments, Interval, Nu
Read More

A

Amisha Kirti

How To Convert Date To Number String Or Text Format In Excel?

article_img
Excel's built-in date system by default supports dates between January 1, 1900, and December 31, 9999. In this range, the numbers start at 1 and increase by 1 each time. In addition to text and numeric data types, Excel also allows dates that are internally stored as serial numbers. In Excel, the date format is typically recognized by default. However, Excel also allows you to input date format by choosing the cell and pressing (Ctrl+;). Methods to Convert Date to Number StringDATEVALUE Function Syntax: =DATEVALUE (date_text) Where date_text is the only argument DATEVALUE FunctionCell FormattingStep 1: Once the cell with the value has been chosen, click the home tab and choose the NUMBER or TEXT option. Step 2: The cell format then changes into the original serial number internally stored by Excel. Note: The first serial number stored by excel is of date 01/01/1900. Methods to Convert Date to TextTEXT Function It is used to convert a date into a specified text format. Syntax: =TEXT(value, format_text) For today’s date: =TEXT(TODAY(), format_text) Where value: The value that needs to be converted. This could be a numeric cell reference or value, or a numeric value returned by
Read More

C

Chetan

How to Round Date to Previous or Next Specific Weekday in Excel?

The WEEKDAY function produces a number between 1 and 7 that corresponds to specific days of the week. WEEKDAY, by default, assumes a Sunday-based week and allocates 1 to Monday, 2 to Tuesday, etc., with 7 given to Sunday. In order to retrieve the prior weekday occurring before a given date in Excel, we must utilize the WEEKDAY function. In order to get the next specified weekday occurring on a certain date in Excel, we must utilize the MOD function. In this article we will learn how to round a date to the previous or next specific weekday. Round Date To Previous Weekday For finding the previous Monday, we are using simple if-else logic with a weekday function i.e., if weekday(date, 2) which returns the first day of the week. So, Monday = 1, Tuesday = 2, ..., Sunday = 7, if that returned value is greater than one then return true. So, we will subtract the given date from the value of Weekday(date, 2) and add 1 to it. If the condition returns false or returned value is not greater than one, we will subtract the given date from the value of Weekday(date, 2) and subtract 6 from it. Follow the further steps to find the previous Monday Step 1: Choose a cell in which to enter the follo
Read More

S

Shivani Jain

How to Change Date Format in Excel

article_img
You know that Excel is one of the best spreadsheets you can work on. Excel is the first preference of many users to create an impressive data set. Have you tried filling in the Date on the sheet? Date with the data is a common practice done by users in Excel to get information timeline. To help organize data in an Excel worksheet, we will discuss four easy yet effective ways to change the date format in Excel. How to Change Date Format in Excel? What is the Date Format in Excel?In Excel, a date is displayed depending on the user's chosen format. You may choose from the different formats available in your Excel worksheet, or you can even create a customized date format per your requirements. The default date format is usually shown in your system's 'Control Panel.' However, you can even change these default settings if necessary. Change Date Format in ExcelWhen you input a date in Microsoft Excel, the program will format it according to the default date settings. For example, if you want to enter the date April 14, 2024, the date could appear in either long date format, or short date format, as 14-Apr, April 14, 2024, 14 April, or 04/14/2024, all depending on your settings. You may
Read More

P

Prachi Sharma

Excel Date Functions with Formula Examples

article_img
There are many functions in Microsoft Excel that may be used to work with dates and timings in Excel. Each function completes a straightforward task, but by combining numerous functions into a single formula, you may handle trickier and more complicated problems. The purpose of discussing DATE functions in Excel is to help different people perform more complex and challenging tasks by combining several functions within one formula. The DATE function is used to calculate dates in Excel. To work with these formulas you first need to insert the dates in Excel. Excel provides different functions to work with dates & times such as TODAY, NOW, WEEKDAY, EOMONTH, etc. which we will discuss here with examples. Excel DATE function ListDATE Function Get the Current date and TimeTODAY- It returns today's date.NOW- It returns the current date and time.Converts Dates to / From textDATEVALUE- Helps to Convert date in the text format to date format TEXT- It helps to convert a date to a text value.Retrieve dates in ExcelDAY- It returns the day of the monthMONTH- It is used to return the month of a specified dateYEAR- It is used to return the year of a specified dateEOMONTH- It returns the last
Read More

A

Ayush Dey

How to Remove Time from Date/Timestamp in Excel?

article_img
Timestamp stores a combined Date and Time value. In this article, we will look at how we can create Timestamp and remove Time from Date in Excel. To do so follow the steps below: Step 1: Formatting data to create a timestamp. Select the cell, right-click on it choose Format Cells... Step 2: Then in the Formal Cells box select Custom in Category and in the type select dd-mm-yyyy hh:mm, and then click Ok. Then, we will enter our date with time in the cell. To remove Time from Date with Format Cells Step 1: Highlight the cells to remove time from date. Step 2: Then right-click on it and choose Format Cells... Step 3:-Then in the Format Cells box select Date in Category and select *14-03-2012 in Type, and then click Ok. After that time is removed from the date. To remove Time from Date with Find And Replace Function Step 1: Highlight the cells to remove time from date. Step 2: Then, click to Home on the Ribbon. In the Editing group, click Find & Select. Step 3: In the resulting menu click Replace... Step 4: Then in Find and Replace box enter the space bar and * in Find what box and leave a blank in Replace with box, then click Replace All. After that time is removed from the date.
Read More

A

Ankit kumar

How to Create a Custom Date Format in Excel?

article_img
Date formatting is very important when you prepare any set of data, then there are different types of formatting used for different categories, which are different from our normal formatting which is dd/mm/yyyy. When you enter any date in MS-Excel then excel considers it as the default format of date as your computer has, but you can change their format in numerous ways, and also you can make your own custom format of a date. Such as 14-Dec, December 14, 2023, 14/12/2023, and may more depending on individual choices, you can change it. Here we are going to show the different ways to format a Date. Let's understand it in a better way by taking an example, Step 1: Open MS Excel and save with any name. Here we are going to save it by "Date Format" name. Step 2: Take any date, like here we are taking 15/04/2001, which is in the format of mm-dd-yyyy and also it is the normal format of a date. Step 3: You can change the format by selecting that particular date cell, and press ctrl+1, then a new "format cells" window will open. From this format window, you can choose date formatting according to your needs. If you want that date to be on 15 April 2001 format, then choose dd mont
Read More

A

Ankur Pal

Excel Date and Time Formats With Examples

article_img
Excel has a built-in time feature that is simple to use and can save you a lot of time. We can insert current Data and Time into a worksheet cell using Excel's built-in functions. When you enter a date or time into a cell, the date and time are displayed in the cell's default date and time format. The regional date and time settings entered in Control Panel determine this default format, which changes when those settings are changed in Control Panel. Numbers can be displayed in a variety of date and time formats, the majority of which are unaffected by Control Panel settings. We can use Excel time features to Convert time to decimal numbersAdd the time of two different unitsCalculate elapsed timeNote: Elapsed time is the amount of time between the start of an event or task to its end. Date and Time Format in ExcelMicrosoft Excel supports the 1900 Date System. In the 1900 data system, the first day supported is January 1, 1900. When we enter a date, excel converts it into a sequential number which represents the number of elapsed days starting from January 1, 1900. Microsoft Excel treats time as a portion of days, and internally it is stored as a decimal fraction. For Example: 00:00
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox