How to Put Google Sheets in Date Order
Learn to sort dates in Google Sheets, convert text dates, handle locale differences, and sort across multiple columns with a step-by-step approach from How To Sheets.
By the end of this guide you will be able to put Google Sheets in date order across single or multiple columns, reliably sort dates, and preserve headers. You'll use built-in tools like SORT, FILTER, and DATEVALUE, plus practical tips for converting text dates and handling locale differences in practice.
How to Put Google Sheets in Date Order: Why it matters
If you routinely track deadlines, schedules, or project timelines, knowing how to put google sheets in date order can save time and prevent missed milestones. According to How To Sheets, sorting dates correctly reduces errors and keeps teams aligned. In Google Sheets, dates are stored as serial numbers beneath the date format, which means a date range sorted by its underlying value will always come in true chronological order. Before you sort, confirm your data is consistent: the date column should contain true dates (not text strings), and any headers should be recognized as headers so they aren’t moved or mixed with data. This foundation matters because many sorting issues stem from inconsistent date formats or mixed data types. When you start with clean data, you unlock reliable sorting that scales as your sheet grows.
The paragraph below outlines the practical steps that will follow, so you can move from theory to action with confidence. In practice, a few minutes upfront to normalize your dates pays dividends later in reporting and automation, especially when you feed sorted data into pivots, charts, or calendar views.
Understanding how Google Sheets stores dates
Google Sheets represents dates as serial numbers—the integer part indicates the day count since a fixed origin, and the fractional part represents time of day. This numeric backing is what makes sorting dates work consistently, even when the visible format changes. By default, Sheets uses the 1899-12-30 origin, and times are measured as portions of a day. Because of this, a date like 2026-02-13 will always be greater than 2026-02-12, regardless of how it’s displayed. When sorting, you’re effectively sorting these underlying numbers, not the text you might see on screen. That’s why dates stored as text or mixed with text values can appear to sort incorrectly. A good practice is to test a small sample of your date column to confirm it sorts as expected before applying to larger ranges.
Basic sort: single-column date order
To sort a single date column in ascending order, you can use either the menu or a formula. Using the formula is powerful because it preserves your original data and creates a sorted view. Example: =SORT(A2:A100, 1, TRUE). The first argument is the range to sort, the second is the sort column (1 for column A if you’re sorting A2:A100), and TRUE means ascending order. If your sheet has a header row, adjust the range to exclude the header or wrap the data with a header-aware function like QUERY. Practically, start with a small sample (A2:A10) to verify the result and copy the approach to larger ranges. This step lays the groundwork for more complex sorts that include multiple columns and criteria.
Sorting with headers and multiple columns
When you have a table (e.g., columns A through D) and want to sort by Date in A while keeping rows intact, you can extend SORT: =SORT(A2:D100, 1, TRUE). This keeps the entire row together, so related data in B, C, and D stay aligned with their dates. If you need to sort by more than one column, you can supply an array of sort columns and directions, such as =SORT(A2:D100, {1,2}, {TRUE, FALSE}) to sort by Date ascending then another column descending. Alternative: use QUERY to sort with more complex criteria while preserving headers.
Handling text dates with DATEVALUE and VALUE
Dates stored as text will not sort chronologically unless converted. Use DATEVALUE to coerce a text date to a date serial number: =DATEVALUE(A2). If your locale uses day-month-year format and DATEVALUE misinterprets the date, consider wrapping with TEXT to normalize: =DATEVALUE(TEXT(A2, "MM/DD/YYYY")). VALUE can also convert numbers stored as text into numeric dates when needed. After conversion, apply a standard sort on the resulting date column. Remember to test a few samples to ensure consistent results across regional settings.
Locale and date formats you need to consider
Date interpretation varies by locale. If your sheet uses dd/mm/yyyy, but your environment expects mm/dd/yyyy, you may see incorrect sorts. In Google Sheets, you can adjust your locale under File > Settings to align with your date format. You can also use helper columns to explicitly format and parse dates (e.g., =DATEVALUE(TEXT(A2,"yyyy-mm-dd"))). Sorting becomes predictable once all dates are stored as true date serial numbers, regardless of how they appear on screen. This is especially important for shared sheets with collaborators in different regions.
Practical examples and templates you can reuse
Example 1: Sort by date ascending with header. If your data is in A1:B100 and A1 is the header, you can use =SORT(A2:B100, 1, TRUE). Example 2: Sort by date descending to show latest events first: =SORT(A2:B100, 1, FALSE). Example 3: Sort by date and then by a second column (e.g., priority in B): =SORT(A2:D100, {1,2}, {TRUE, FALSE}). These templates adapt to most daily needs, from task lists to event calendars. Remember to test with your real data before applying to extensive ranges.
Troubleshooting common sorting problems
If dates still sort incorrectly, check for mixed data types in the date column (numbers, text, or blanks). Ensure there are no leading spaces or non-breaking characters. If you have formulas that generate dates, verify they return true date values rather than text. When sorting large data sets, performance may slow down; consider sorting in chunks or using a secondary helper sheet to validate results before pushing to production sheets. Finally, always back up your data before running batch sorts on important tables.
Tools & Materials
- Computer or device with internet access(Must be able to access Google Sheets)
- Google account with access to Google Sheets(Required for editing and saving sheets)
- Sample date data set(Include both proper dates and text-form dates for testing)
- Browser with up-to-date JavaScript(Chrome/Edge/Firefox recommended)
Steps
Estimated time: 20-30 minutes
- 1
Open your Google Sheet with dates
Navigate to the sheet that contains your date column. Ensure headers are clearly labeled and the date column contains actual date values or texts that can be converted to dates.
Tip: If your data is in multiple ranges, consider consolidating into a single contiguous block before sorting. - 2
Check date formatting
Scan the date column for obvious text dates or inconsistent formats. If you find mixed formats, plan to convert them using DATEVALUE or VALUE as needed.
Tip: Use a helper column to test conversions before applying to the main data. - 3
Convert text dates to real dates
If you see dates stored as text, apply DATEVALUE or wrap with TEXT as needed to guarantee they are true date serial numbers. This step is crucial for correct sorting.
Tip: Do this in a separate column first to verify results. - 4
Sort a single column
Select the date range and apply a sort formula like =SORT(A2:A100, 1, TRUE) to arrange dates ascending. If you have headers, adjust the range accordingly.
Tip: Test on a small sample before extending to larger ranges. - 5
Sort a table with headers
To sort an entire table by date while preserving rows, use =SORT(A2:D100, 1, TRUE). This keeps associated data intact.
Tip: Make sure the header row isn’t included in the range to sort. - 6
Sort by date and another column
If you need multi-criteria sorting (e.g., by date then priority), use =SORT(A2:D100, {1,2}, {TRUE, FALSE}).
Tip: Date has the primary authority; secondary sort helps when dates tie. - 7
Handle locale differences
If your sheet uses a non-default locale, adjust File > Settings > Locale to ensure dates are interpreted correctly. Consider explicit DATEVALUE conversions for reliability.
Tip: After changing locale, re-check your existing data. Sorting behavior may change. - 8
Validate results
Check a few sample rows to confirm dates sorted as expected. Look for any anomalies like 1900-era dates or blank rows interfering with order.
Tip: Keep a rollback plan in case you need to revert. - 9
Apply to the entire dataset
Once you’re confident, apply the sort approach to the entire data range. Consider using an array formula or a script if you need automatic sorting on data entry.
Tip: Document the method used so teammates can reproduce it. - 10
Create a reusable template
Turn your sorted approach into a template with named ranges and clear instructions so future sheets can reuse the method without starting from scratch.
Tip: Include notes on any locale-specific considerations.
FAQ
What happens if dates are stored as text?
Dates stored as text will sort lexicographically, not chronologically. Convert them using DATEVALUE or VALUE and then sort by the resulting date numbers.
Text dates won’t sort correctly. Convert them to real dates and then sort.
How do I sort by date across multiple columns?
Use a multi-criteria sort like =SORT(A2:D100, {1,2}, {TRUE, FALSE}) to sort by date first, then by a secondary column. Or sort the entire range by the date column while keeping other columns in place.
Sort by date first, then by the next criterion you choose.
How can I sort without disturbing headers?
Exclude the header row from the sort range or use a formula like SORT on the data range excluding headers. You can reattach the header after sorting.
Sort only the data, then re-add the header row if needed.
What if some dates are blank?
Blank cells sort to the top or bottom depending on sort direction; consider filtering them out or filling blanks with a placeholder date before sorting.
Handle blanks before sorting to avoid unexpected gaps.
Can I automate sorting on data entry?
Yes, you can use a script or an array formula approach to automatically output a sorted view of your data. This avoids manual re-sorting after every update.
Automation keeps data sorted without manual steps.
Watch Video
The Essentials
- Dates are stored as serial numbers; sorting sorts by the actual date value.
- Convert text dates to real dates before sorting for reliable results.
- Sort tables to keep rows intact and maintain data integrity.
