Google Sheets: Format a Cell as a Date

To format a cell as a date in Google Sheets, select the cell or range, then choose Format > Number > Date. This changes how a date value appears; if the cell contains text that Sheets does not recognize as a date, formatting alone will not convert it.

Apply a date format

In Google Sheets in a web browser, select one cell, a group of cells, or an entire column, and use the menu path Format > Number > Date. Sheets applies its standard date display to the selection. For example, a recognized date might appear as 11/3/2026 or as a different date order, depending on the spreadsheet’s locale and formatting settings.

You can also use the toolbar’s number-format controls if they are visible, but the menu is a reliable way to find the date option. To format a complete column, click its letter before choosing the date format. This is useful when preparing a column for dates you will enter later, though it does not guarantee that every pasted value will be interpreted as a date.

For a keyboard alternative, Google Sheets supports Ctrl+Shift+3 on Windows and ChromeOS to apply a date format. On Mac, the corresponding shortcut is Command+Shift+3, although macOS shortcuts or browser settings can sometimes take precedence. If the shortcut does not work, use the menu instead.

Formatting is different from converting

A cell’s displayed format and its underlying value are separate things. If Sheets already recognizes the contents as a date, applying a date format changes the display without changing the date itself. For instance, a date may be shown as 3-Nov-2026 in one format and 11/3/2026 in another, while remaining the same date value for calculations, sorting, and date-based filters.

But a cell can contain text that merely looks like a date. A pasted value such as an unrecognized date string may stay as text after you choose Format > Number > Date. A useful clue is alignment: text often sits to the left by default, while numbers and recognized dates often sit to the right. Alignment is only a clue, not a definitive test, because cells can be manually aligned.

When conversion is needed, first check whether the text has a consistent, recognizable pattern. For a date in A2 that Sheets can parse under the spreadsheet’s locale, enter =DATEVALUE(A2) in another cell. The result is a date value, which you can then format as a date. If the source contains dates in mixed orders or ambiguous forms, inspect and standardize the data rather than assuming the formula has interpreted every row correctly.

Choose the display that fits your data

The best display depends on who will read the sheet and what the date is used for. A short numeric format saves space, while a month written as a word can make reports easier to scan. For a dataset shared across regions, a format that spells out the month can also reduce confusion about whether a value such as 04/05/2026 means April 5 or May 4.

Compact lists: use a short date style when column width matters and readers share the same date convention.
Reports: use a month name, such as 3 Nov 2026, when readability matters more than compactness.
Schedules: include the weekday if readers need to plan by day, for example Tue, Nov 3, 2026.
Monthly summaries: show month and year when the day is not meaningful, such as Nov 2026.

A display format does not remove information from a real date value. Showing only a month and year does not turn the value into a month-only object: the underlying date still includes a day. That distinction matters in formulas and sorting. If every entry represents a month, choose a consistent convention for the underlying day, such as the first day of each month, and make the display clearly show only the month and year.

Use a custom date format

If the standard Date option does not show the information you want, select the cells and choose Format > Number > Custom date and time. The dialog lets you build a display from date and time components and choose their order. You can create formats that include a weekday, a full month name, or a four-digit year, for example, a display similar to Tue, November 3, 2026.

Custom formatting is useful when a sheet needs a consistent presentation without changing its values. You might use a long date in a customer-facing schedule and a compact date in a working table. The formatting is attached to the selected cells, so entering another recognized date in one of those cells generally displays it using that format too.

Take care not to confuse a date format with plain text. If you type a date into a cell as text by starting with an apostrophe, or if an import has stored it as text, a custom display format cannot rearrange the characters into a real date. Convert the data first, then apply the display format. Also remember that copying and pasting can bring formatting from the source; if a value looks unexpected, check its number format and the cell’s actual contents.

Check locale and ambiguous dates

Google Sheets uses the spreadsheet’s locale to influence date conventions and how entered values are interpreted. To review it, open File > Settings and check the Locale setting. A change can affect default date, number, and currency formats across the spreadsheet, so consider the impact on other users and existing data before changing it.

Ambiguous numeric dates are a common source of mistakes. The string 03/04/2026 could mean March 4 or April 3, depending on the convention used when it was entered or imported. Applying a different display later may reveal a different-looking date, but it cannot reliably tell you what the source author intended. For shared files or imported records, prefer an unambiguous input convention, such as a month written as a word or a consistently documented year-month-day format, and verify a few sample rows after import.

Changing the locale is not a universal fix for data already entered incorrectly. It may alter the way Sheets displays or interprets values, but you should confirm the actual date against the source. If a column mixes dates and text, standardize it row by row or in a helper column before relying on date calculations.

Create dates with formulas

For dates built from separate year, month, and day fields, the Google Sheets DATE function creates a date value. With the year in A2, month number in B2, and day number in C2, use =DATE(A2,B2,C2). For example, if those cells contain 2026, 11, and 3, the formula returns the date November 3, 2026. Apply a date format to the formula cell to control how it appears.

For a fixed date in a formula, =DATE(2026,11,3) returns November 3, 2026 as a date value. This is generally more dependable than typing a date string into a formula when a spreadsheet may be used under different regional conventions. In contrast, the TEXT function produces text, not a date value. That can be handy for a label, but text results are not interchangeable with date values for date arithmetic and some sorting or filtering tasks.

Remember that a date is useful as a value, not just a visual label. If a formula needs to calculate the number of days between dates, or compare a deadline with today, keep the cells as date values and use formatting only for presentation. Avoid converting dates to text unless the result is specifically meant to be a label or a text export.

Fix dates that look wrong or will not sort

If a formatted cell shows a number instead of a familiar date, the value may still be a valid date stored as a serial number, but the cell may have a number format such as Number or Automatic. Select it and choose Format > Number > Date. If the result is still unexpected, inspect the formula bar and test a nearby known date so you can distinguish a display issue from a wrong underlying value.

If dates sort in an order that seems alphabetical rather than chronological, some entries may be text while others are date values. A column that visually contains dates can still have mixed data types. Check representative cells, convert text consistently, and then sort the complete table using the date column. Sorting only one column can separate dates from their associated names or records, so select the whole data range before sorting.

If a date is left-aligned or does not respond to date formatting, check whether it was pasted as text.
If month and day appear reversed, verify the source convention and spreadsheet locale before editing records.
If a formula result displays as a serial number, apply a date format to the result cell.
If only some rows behave differently, look for mixed formats, extra spaces, or inconsistent source data.

How dates work in Google Sheets

Google Sheets stores recognized dates as numeric values, which is why dates can be compared, sorted chronologically, and used in calculations. The displayed date is a formatted view of that value. Time can also be represented as part of a date-time value; the date portion identifies the day, while a fractional portion can represent a time of day. If a cell contains a time as well as a date, choosing a date-only display can hide the time without necessarily removing it from the value.

This also explains why a date-looking number can appear when formatting is lost, and why changing a format does not repair incorrect source data. Formatting answers, “How should this value look?” Conversion answers, “What value does this text represent?” Keeping those tasks separate makes spreadsheets more dependable and helps prevent errors in charts, filters, and formulas.

Frequently Asked Questions

How do I change the date format in Google Sheets?
Select the cells, then choose Format > Number > Date for the standard date style, or choose Format > Number > Custom date and time to build a different display.

Why does formatting a cell as a date not work?
The cell may contain text rather than a recognized date value. Confirm the source format and locale, convert the text into a date value if possible, and then apply the date format.

How do I show the month name instead of a number?
Select the cells and open Format > Number > Custom date and time. Choose a format that uses a written month name, then apply it to the range.

Does changing a date format change the date itself?
No. Formatting normally changes the display, not the underlying date value. A separate conversion or formula is needed when the cell contains text or the wrong date.

Can I enter dates in Google Sheets using a formula?
Yes. Use =DATE(year,month,day) with numeric year, month, and day arguments. For example, =DATE(2026,11,3) creates November 3, 2026; format the result cell as needed.

Google Sheets and Excel date formatting

Google Sheets and Microsoft Excel both distinguish a date’s underlying value from its display, but their menus and some formatting details differ. The instructions here use Google Sheets in a web browser: Format > Number > Date and Format > Number > Custom date and time. In Excel, date formats are chosen through the Home tab’s number-format controls, so Excel ribbon instructions should not be substituted for the Sheets menu path.

When sharing a spreadsheet between the programs, verify date displays and a few underlying values after importing or exporting. A format may look different under another program’s locale, and text dates can be interpreted inconsistently. The safest workflow is to keep dates as real date values, use a clear display, and confirm that ambiguous source dates match the intended calendar dates.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.