While making entries in your Excel spreadsheet, you may not have enough time to sort dates beforehand. Although it may sound like a tedious process of sorting dates out, it can be quite useful to keep track of deadlines, birthdays and anniversaries.
Excel, being one of the most used spreadsheets, offers features including a tool and formulas to make this task easier for you. This way, you do not have to go through the hassle of manually sorting each date out. If you’re interested in sorting dates in Excel according to weeks, months, days, and years, keep reading to get the dates sorted in no time!
Before You Start Sorting Dates
You must keep a few things in mind before sorting your data. For example, make sure that you are using a valid date format. You can change your date format from the Format Cells tab (Ctrl + 1 > Date).
The cell that contains your date is in the Date or General format. You can check the cell format from the Home tab. If the cell is not in the date format, you can select the fly-out menu in the Number section and choose Date or General.
How to Sort Dates in Excel?
You can use the Sort & Filter tool to sort cells from ascending to descending order and vice-versa. However, you do not have the flexibility to sort dates by days, weeks, and months through this filter. You will have to use the library functions Excel offers to sort dates in this manner.
Use Sort and Filter Tool
The sort and filter tool is a quick way to sort dates from highest to lowest value. This tool sorts the dates according to the year. You will have to choose different options depending on whether you want to sort your dates according to ascending or descending order.
- On your workbook, select the cell range with the dates.
- Head to the Data tab.
- Select Sort from the Sort & Filter section.
- Under Order, select the fly-out menu.
- To sort dates in ascending order, choose Newest to Oldest.
- For descending dates, select Oldest to Newest.
- Click OK.
Use Excel Functions
If you wish to sort dates out by days, weeks, and months, you will first have to extract these data using the library functions. Then, you can use the Sort & Filter tool to sort your data from ascending to descending order or vice-versa. The following methods use dates in the mm/dd/yyyy format.
Sort Dates by Days
You might find sorting dates by days useful while working with deadlines. This way, you can view your employees’ approaching deadlines. Before entering the formula, ensure you have correctly entered your dates.
On your worksheet, create a next column named “days”. On the first empty cell on the column, enter the formula in the following format:
=DAY(cell)
Sorting Dates by Weeks
If you are dealing with longer deadlines, you can sort the dates by week. Create a new column named “weeks” and enter the formula in the following format on the first cell of the columns:
=WEEKDAY(cell)
Sorting Dates by Months
You would want to sort your dates by month to keep track of birthdays, anniversaries, or annual events. Create a new column named “Months” on the worksheet with the dates you want to sort dates by month. On the first cell in the Months column, enter the formula in this format:
=MONTH(cell)
How to Create a Macro to Automatically Sort Date in Excel?
Macros are an amazing way to automate tasks in Excel. If you frequently need to sort dates out in Excel, you can record a Macro and run it the next time you want to get the job done. This will save you a lot of time compared to repeating these methods manually.
You can start recording a Macro from the Developer tab. After you hit record, you can carry on to continue the above-mentioned methods. Remember that all your actions are recorded, so only perform actions you wish to automate. Excel will record your keystrokes and save them as a Macro in the Visual Basics code.
