How To Sort By Date In Google Sheets

Understanding Why Dates Don’t Sort Correctly

Google Sheets treats data as either text or numbers, and this distinction affects how dates sort. When dates are formatted as text instead of actual date values, Sheets sorts them alphabetically rather than chronologically. A date like “January 15, 2026” and “February 5, 2026” might sort in the wrong order because “January” comes alphabetically before “February,” but that’s text sorting, not date sorting.

Similarly, dates entered as “1/15/2026” and “2/5/2026” may sort correctly if Sheets recognizes them as dates, but if they’re stored as text strings, they could sort in completely unexpected ways. The key is ensuring Sheets interprets your data as actual dates, not as text that happens to contain numbers and slashes.

Another common issue occurs when importing data from other sources like Excel or databases. The data might arrive in an unusual date format that Sheets doesn’t automatically recognize. A date in the format “2026-04-25” might be treated as text even though it’s clearly a date to human eyes. This becomes apparent when you sort and the dates don’t arrange in the correct chronological order.

How Google Sheets Stores Dates Internally

Google Sheets stores dates as serial numbers. Each date has an associated number representing the number of days since December 30, 1899. For example, January 1, 1900 is stored as 1, January 2, 1900 is stored as 2, and so on. Today’s date, April 25, 2026, is stored as a much larger number like 46047.

This internal numeric representation is why sorting dates actually works correctly when they’re properly formatted as dates. Sheets sorts these serial numbers in numeric order, which results in dates being sorted chronologically. The earlier the date, the smaller the serial number, so ascending order naturally produces oldest-to-newest sorting.

When you see a date displayed as “April 25, 2026,” that’s the formatted display of the underlying serial number. The cell actually contains the number, and the formatting simply tells Sheets how to display it to you. Understanding this internal structure helps explain why proper formatting is essential for correct date sorting.

Ensuring Dates Are Recognized as Dates, Not Text

To verify whether your dates are actual date values or text, click a cell containing a date. Look at the formula bar at the top of your screen. If you see a formula like “=DATE(2026,4,25)” or just a number like “46047” with date formatting applied, it’s a proper date. If you see the text exactly as it appears in the cell, like “April 25, 2026” with no formula, it might be text.

Alternatively, right-click the cell and select “Format cells.” Look at the Format menu and check what format is applied. If it says “Date,” the cell is formatted as a date. If it says “Text” or “Plain text,” that’s your problem. You’ll need to convert the text to actual date values.

To convert text that looks like dates into actual dates, you have several options. The simplest is using the DATEVALUE function. If cell A1 contains the text “04/25/2026,” you can create a formula in another column: =DATEVALUE(A1). This converts the text to an actual date value. Then copy the results and paste them back as values to replace the original text.

Another approach is to use Find and Replace with regular expressions to clean up dates in unusual formats, then apply date formatting to the column. Select the column, open Find and Replace, and use a pattern that matches your date format, then replace with a normalized format Sheets recognizes immediately.

Google Sheets has flexibility in how you manipulate data, including date conversion techniques. You can also use the VALUE function to force Sheets to interpret text as numbers and dates.

Sorting a Single Column by Date

To sort a column containing dates, select all the data in that column, including any headers. Open the Data menu and click “Sort range.” If your data has a header row, check the box that says “Data has header row.”

A dialog box appears with options for how to sort. Select the column containing your dates. Choose whether to sort A to Z (ascending, oldest to newest dates) or Z to A (descending, newest to oldest dates). Click “Sort.”

Sheets applies the sort and reorganizes all rows accordingly. The dates should now display in chronological order. If the sort doesn’t work as expected, it’s likely because the dates are still formatted as text, in which case you need to apply date formatting first.

For a more direct approach, right-click a cell in the date column and select “Sort sheet A to Z” or “Sort sheet Z to A” from the context menu. This sorts the entire sheet based on the dates in that column without opening the sort dialog, which is faster for simple sorting tasks.

Sorting With a Header Row

When your data includes a header row (like “Date,” “Name,” “Amount”), it’s crucial to exclude the header from the sort. If you include the header in the sort, it might move to the middle of your data, breaking the structure and making your spreadsheet unusable.

When using the Data > Sort range option, check the “Data has header row” checkbox. This tells Sheets to treat the first row as a header and not include it in the sort operation. The header stays at the top while your data sorts below it.

If you right-click to access the quick sort menu, Sheets automatically detects whether your data has a header and handles it appropriately. The quick sort is often the faster option for simple sorts, but the sort range dialog gives more control for complex sorting requirements.

Sorting Multiple Columns by Date and Other Criteria

You often need to sort by multiple columns. For example, you might want to sort by date first, then by name for any dates that are identical. Open the Data menu and select “Sort range.” This opens a dialog with more granular control over sorting behavior.

The dialog shows all your columns. Drag the date column to the top position. This becomes the primary sort criterion. Select whether to sort ascending or descending based on what you need.

Click “Add another sort column” and select the second column (like name). Choose its sort order. You can add additional sort columns as needed. Sheets applies the sorts in order, so primary sorting happens first, then secondary, and so on. This creates a hierarchical sort where related data groups together.

When you have multiple people with the same meeting date, they now sort alphabetically by name within that date. This is especially useful for organizing employee data, event schedules, or project timelines where you need multiple levels of organization.

Using Filter View to Sort Dates Without Affecting Others

A filter view lets you sort and filter data without affecting other views of the same spreadsheet. This is useful when multiple people work on the same sheet and you don’t want your sorting to change the view for everyone else permanently.

Select the data range you want to filter. Open the Data menu and click “Create a filter.” Sheets adds filter buttons to your header row. Click the filter button in the date column to access sorting and filtering options.

Select “Sort A to Z” or “Sort Z to A” to sort the dates. The data appears sorted in your view, but the underlying sheet data doesn’t change. Other people viewing the sheet see the original order unless they create their own filter view.

You can create multiple filter views on the same data. Each filter view can have different sorts and filters applied, all independent of each other. This is particularly powerful in team environments where different people need different perspectives on the same data.

Sorting Dates in a Pivot Table

Pivot tables organize data differently, and sorting dates in a pivot table requires a slightly different approach. In your pivot table, locate the date field that’s listed as a row or column label.

Click the date field label and look for sort options. You can usually click the label itself or right-click it to access sorting options. Choose to sort ascending (oldest to newest) or descending (newest to oldest).

If the sort option doesn’t appear, make sure your dates are formatted as dates, not text. Pivot tables sometimes struggle with text-formatted dates and may not offer sorting options for such fields.

You can also configure the pivot table’s sort options through the Pivot Table Editor panel on the right side of your screen when the pivot table is selected. This provides a dedicated interface for managing all pivot table sorting and grouping behavior.

Sorting by Month Only or Year Only

Sometimes you need to sort by just the month and year, ignoring the day. Or sort by just the year. You can accomplish this using formulas to extract the month or year, then sort by those values.

To extract the month from a date, use the MONTH function: =MONTH(A1) returns a number from 1 to 12 representing the month. To extract the year, use the YEAR function: =YEAR(A1) returns the year as a four-digit number. Using these functions, you can create columns that extract just the information you need for sorting.

Create a helper column with a formula like =MONTH(A1) or =YEAR(A1) for each date. Then sort by this helper column. Delete the helper column after sorting if you don’t need it. This approach gives you complete control over which date component determines the sort order.

Alternatively, apply custom date formatting to display only the month and year, then sort. Right-click your date column, select “Format cells,” and choose a custom format that displays only month and year like “MMM YYYY.” Then sort normally, and Sheets will sort based on the actual dates while displaying only the formatted view.

Using the SORT Function for Formula-Based Sorting

The SORT function lets you create a sorted list without modifying the original data. Instead of sorting data in place, SORT creates a new sorted result elsewhere in your sheet, preserving your original data.

The basic syntax is =SORT(range, sort_column, ascending). For example, if your dates are in column A from rows 1 to 20, the formula =SORT(A1:A20, 1, TRUE) creates a sorted list with dates in ascending order (oldest to newest). Change TRUE to FALSE for descending order (newest to oldest).

If you want to sort multiple columns together, include all the data in the range. For example, =SORT(A1:C20, 1, TRUE) sorts the data by the first column (dates) while keeping the corresponding data in columns B and C aligned. This ensures that names, amounts, or other associated data stay with the correct date.

The SORT function is non-destructive. Your original data remains unchanged, and the sorted result appears in a new location. This is valuable when you need multiple different sort orders or when you’re experimenting with data organization.

Using the QUERY Function to Sort by Date

QUERY is a powerful function that retrieves and sorts data based on SQL-like syntax. You can use QUERY to sort dates alongside filtering, grouping, and other operations in a single formula.

The basic syntax is =QUERY(range, “SELECT * ORDER BY column ASC”). For example, =QUERY(A1:C20, “SELECT * ORDER BY A ASC”) sorts by column A (your dates) in ascending order from oldest to newest.

Change ASC to DESC for descending order. You can add LIMIT to restrict the number of rows returned, or WHERE clauses to filter data based on criteria. For example, =QUERY(A1:C20, “SELECT * WHERE B > 1000 ORDER BY A DESC”) returns rows where column B is greater than 1000, sorted by column A in descending order (newest to oldest).

QUERY is particularly useful when you need to sort and filter simultaneously. It’s more powerful than simple sorting when you need to apply multiple conditions or transformations to your data in one formula.

Sorting by Date of Birth or Age

If your spreadsheet contains dates of birth, sorting by age (calculated from the date of birth) requires a formula approach. Create a helper column that calculates age from the date of birth using a formula like =DATEDIF(A1, TODAY(), “Y”), which calculates the complete years between the birthdate and today.

Then sort by the age column as you would any other numeric column. The data sorts by age, and the corresponding dates of birth stay aligned with each person’s record.

Alternatively, sort directly by the date of birth column if you want people listed from oldest to youngest. Oldest dates sort first (ascending), youngest last. This approach is simpler and preserves the actual date of birth for reference.

Auto-Sorting When New Data Is Added

When new rows of data are added to your sheet, you might want them to automatically sort by date. Google Sheets doesn’t automatically re-sort data when new rows are inserted, but you can set this up using Apps Script.

Open the Tools menu and select “Script editor.” This opens a new Google Apps Script project. Write a script that uses the onEdit trigger to monitor when data changes and automatically resorts by date whenever new data is added.

A basic script might look like this: when any cell is edited, the script selects your data range, applies a sort by date, and saves the changes. This requires some coding knowledge, but pre-written scripts are available in the Apps Script gallery that you can adapt for your needs.

Alternatively, use the SORT function as described earlier to create a sorted view that updates automatically whenever your source data changes. This formula-based approach requires no coding and updates dynamically.

Common Date Sorting Issues and Solutions

Dates sorting as text instead of in chronological order is usually the result of the data being formatted as text. To fix this, select the date column, open the Format menu, click “Number,” and select “Date” from the list. Sheets reformats the cells as dates, and sorting should work correctly afterward.

Mixed date formats in the same column can confuse Sheets. Standardize all dates to the same format before sorting. Use Find and Replace to convert all dates to a consistent format, then apply date formatting to the entire column.

Dates that are actually formulas might not sort as expected. If your dates are created with formulas like =DATE(2026,4,25), they should sort correctly, but if the formulas produce text instead of actual date values, sorting fails. Check that your date formula produces a date, not text by examining the formula bar.

Blank cells in your date column can cause unexpected sorting behavior. Sheets sometimes treats blanks inconsistently, placing them at the top, bottom, or middle of your sort depending on the sort direction. Delete blank rows or fill blanks with a placeholder value before sorting.

Date Formats in Different Locales

Google Sheets respects your locale settings when interpreting dates. A date like “04/25/2026” in the US locale means April 25, but in European locales like Germany or France, it might mean the 4th of the 25th month (which doesn’t exist). This causes errors and sorting failures.

To avoid confusion, use a locale-neutral date format like “2026-04-25” (YYYY-MM-DD) or enter dates using the DATE function: =DATE(2026, 4, 25). These formats work consistently regardless of locale settings or regional configurations.

If your sheet is used internationally, be explicit about date formats. Include a key or header explaining the format you’re using. This prevents misunderstandings when people from different regions view or edit your data.

Converting Text Dates to Real Dates

The DATEVALUE function converts text that represents a date into an actual date value. If your sheet has a column of text dates like “April 25, 2026,” the formula =DATEVALUE(A1) converts it to a date value that sorts correctly.

Create a helper column with DATEVALUE formulas for all your text dates. Then copy the results, right-click, and paste as values only. This replaces the original text with actual date values. Delete the helper column afterward.

For dates in unusual formats that DATEVALUE doesn’t recognize, you might need to use a combination of functions. For example, if your dates are in the format “25-Apr-2026,” you can use a combination of FIND and DATE functions to extract the day, month, and year separately, then reconstruct them as a proper date using the DATE function.

Google Sheets’ flexibility means you can handle almost any date format, though it might require more complex formulas. The effort invested in proper date conversion pays dividends when you need to sort, filter, or analyze date-based data.

Best Practices for Date Sorting

Always format dates properly before attempting to sort. Use the Format menu to apply date formatting explicitly. Don’t rely on Sheets to automatically recognize text that looks like dates.

Use a consistent date format throughout your spreadsheet. If some cells have “April 25, 2026” and others have “2026-04-25,” convert them all to the same format before sorting. Consistency prevents errors and confusion.

Create backups of your data before performing large sorts. While sorting doesn’t destroy data, accidental clicks can lead to unexpected results. A backup lets you recover if something goes wrong.

Use helper columns when you need complex sorting operations. Keep original data intact and use formulas to create sorted views elsewhere.

Test your sorts on a small sample of data first. Verify that the sort is working correctly before applying it to your entire dataset. This prevents errors that could affect large amounts of data.

Summary and Next Steps

Sorting by date in Google Sheets requires that your dates are formatted as actual dates, not text. Use the Format menu to apply date formatting, and use DATEVALUE or DATE functions to convert text dates to proper date values.

The Data > Sort range menu gives you control over how your data sorts. Use it to sort by multiple columns, configure header rows, and choose ascending or descending order. For more advanced sorting without modifying your original data, use SORT or QUERY functions.

Understand how Sheets stores dates internally as serial numbers, and you’ll understand why sorting works the way it does. With these tools and techniques, you can organize date-based data in almost any way you need for analysis, reporting, or collaboration. Whether you’re managing project timelines, organizing sales data, or tracking event schedules, proper date sorting is essential.