Google Sheets Date Format: How to Turn It On and Set It

To turn on date formatting in Google Sheets, select the cells you want to format, then choose Format > Number > Date. To show dates in a particular style, use Format > Number > Custom date and time. This changes how recognized date values appear; it does not, by itself, turn text into real dates or change the format of every cell in the spreadsheet.

What You’ll Need

A Google account and access to the spreadsheet in a web browser.
The cells or column where you want dates to appear. You can format an empty range before entering dates, or format cells that already contain dates.
If the dates were copied or imported, an idea of their intended order, such as month/day/year or day/month/year. This matters when a date could be read more than one way.

Step-by-Step

Open the spreadsheet and identify the date cells. Open Google Sheets in your browser and select the spreadsheet. Decide whether the format should apply to one cell, a group of cells, or a whole column. Applying a format to the intended range keeps unrelated values, such as order numbers or product codes, from displaying as dates.
Select the cells or range. Click a cell to format just that cell. To select a continuous range, click the first cell and drag to the last, or click the first cell and hold Shift while selecting the last. To format an entire column, click its letter at the top of the sheet. You can select several separate ranges by holding Ctrl on Windows or Command on Mac while selecting them.
Apply the standard date format. With the range selected, open Format in the menu bar, point to Number, then choose Date. Any values Sheets recognizes as dates should display in the spreadsheet’s date style. The exact appearance can depend on the spreadsheet’s locale, so a date may appear with a different order or separator than you expect.
Check that the result is sensible. Look at several cells, especially dates where the day is 12 or lower. A value such as April 7 can be confused with July 4 if the source data and spreadsheet use different date conventions. If a cell displays a serial-looking number, a different date, or no change at all, see the troubleshooting section before formatting the rest of the sheet.
Choose a custom date style if needed. Select the cells, then choose Format > Number > Custom date and time. In the dialog, choose or edit a pattern, then apply it. For example, a pattern like dd mmm yyyy can show a date as 07 Apr 2026, while mmmm d, yyyy can show it as April 7, 2026. The available choices and preview help you confirm the result before applying it.
Set the order and detail you want. A numeric style such as yyyy-mm-dd is often easier to scan in lists because the year comes first. A style with a spelled-out month, such as d mmmm yyyy, can make reports clearer to readers. In the custom-format dialog, use the date components and separators to control whether the month is numeric or written out, whether day and month have leading zeros, and whether the year uses two or four digits.
Format future entries too. If you expect to add dates later, select the cells below your current data or click the column letter to select the full column before applying the date format. When entering a new value, use a date that Sheets can recognize under the spreadsheet’s locale. Formatting an empty cell prepares its display, but it does not fill in a date for you.
Review the spreadsheet locale if dates are interpreted incorrectly. If the sheet consistently reads dates in the wrong order, open File > Settings and check the Locale setting. The locale affects how Sheets interprets date entries and its default date, number, and currency conventions. Change it only when the spreadsheet should use a different regional convention; it can affect other people working in the same spreadsheet as well.

The main distinction is between a date value and its display format. A recognized date is stored as a value that Sheets can use for sorting and calculations. Applying Format > Number > Date changes how that value looks, not the date itself. If the cell contains plain text that only resembles a date, changing its number format may have no visible effect. Confirm that the value is recognized before assuming the format menu has failed.

Common Mistakes

Formatting the wrong range: Selecting only the header or one cell leaves the rest of the column unchanged. Check the highlighted selection before opening the menu. If you want the whole column, click its letter rather than dragging over only the filled rows.
Expecting a number format to convert text: A typed or imported string may look like a date while remaining text. Applying Date changes the appearance of recognized date values; it is not a reliable text-conversion tool. Re-enter the value in a form Sheets recognizes, taking the spreadsheet locale into account.
Mixing up day and month: Dates such as 03/04/2026 are ambiguous. They can mean March 4 or April 3 depending on the convention. Confirm the source format before converting or sorting a large list, and use a month name when entering a date manually if that avoids ambiguity.
Assuming the format changes the underlying date: A cell can display a date style while keeping the same stored value. If the date itself is wrong, choose the correct date value; changing the display pattern will not correct it.
Changing the locale to fix just one column: Locale is a spreadsheet-level setting, not a local formatting switch for one selected range. For a single column, use a date format or custom date pattern instead. Consider the locale setting only when the sheet’s regional conventions as a whole need to change.
Using a format that hides useful information: A two-digit year or numeric-only date may be compact but can be unclear in a long-term record or when sharing data. Prefer a four-digit year for schedules, invoices, and archived records.

Tips

Keep dates consistent within a column. Applying one format to the full date column makes a schedule easier to scan and reduces confusion when dates are sorted or reviewed.
Use a written month for human-readable reports. A display such as 7 Apr 2026 makes the month clear to readers using different regional conventions. For data that will be exchanged with other systems, a year-first pattern such as yyyy-mm-dd can be easier to interpret.
Separate dates from times when appropriate. If cells need to show both a date and a time, choose a date-and-time pattern in the custom-format dialog rather than assuming a date-only style will display the time. The underlying value may include a time even if the current display hides it.
Preserve the original data before a bulk change. If a column was imported with an uncertain date order, make a copy of the sheet or duplicate the column before changing values or locale. This gives you a reference if the conversion does not produce the intended dates.
Remember that this is a display setting. Other people viewing the same spreadsheet generally see the cell formatting you apply. If someone needs a different presentation, discuss whether the sheet should use one shared standard or separate sheets for different audiences.

Troubleshooting

The cell still looks unchanged after choosing Date. First confirm that you selected the cell containing the value. If the entry is text rather than a recognized date, the format will not convert it. Edit the cell and enter the date in a form that matches the spreadsheet’s locale, then apply the date format again. For example, spelling out the month can avoid uncertainty when you enter a single date manually.

A number appears instead of a date. The value may be numeric and currently displayed without a date format, or it may be a number that is not meant to represent a date. Select the cell and choose Format > Number > Date only if the value is intended to be a date. If the resulting date is far from the expected one, undo the change and check the original value rather than treating every number as a date.

The date appears in the wrong order. Check the spreadsheet locale through File > Settings and confirm how the source data was written. Do not guess with ambiguous entries such as 05/06/2026. If you are responsible for the spreadsheet, choose the appropriate locale before re-entering or importing the dates. If you are not sure which convention the source uses, ask the data provider before making a bulk change.

Only some cells change. Some values in the selection may be real dates while others are text, blank, or unrelated numbers. Check the problem cells one at a time and compare how their contents were entered. Re-enter text dates as valid date values, then apply the same format to the complete range again. Also check whether the intended cells were included in the selection.

The format is correct, but new dates look different. Confirm that the new entry is being made inside the formatted range. If it is, check that the entry is recognized as a date and that a custom pattern was not applied to only part of the column. Selecting and formatting the full column can help keep later entries consistent, but it will not correct text entries that Sheets cannot interpret.

You cannot find the same menu as in a tutorial. These instructions are for Google Sheets in a web browser. Use the menu bar’s Format > Number path; do not follow Excel ribbon instructions, which apply to a different program. If the menu is compact or the window is narrow, widen the browser or inspect the Format menu carefully for the Number submenu.

Frequently Asked Questions

How do I apply date format to an entire column?
Select the column by clicking its letter, then choose Format > Number > Date. This applies the display format to existing values and prepares empty cells in that column to display recognized date entries in the same style.

Can I make Google Sheets show dates as day/month/year?
Yes. Select the range and open Format > Number > Custom date and time. Choose a pattern with the day before the month, or edit the pattern to show the order you need. Check the preview and confirm that the format is clear to the people who will read the sheet.

Why does Google Sheets turn my date into a number?
Dates are stored as values, and a number display can reveal that underlying value. Apply Format > Number > Date if the value is intended to represent a date. If the result is not the expected date, check the original entry and locale before continuing.

Does formatting a cell change the date used in calculations?
No. A number format changes the displayed appearance, not the stored date value. To correct an incorrect date, edit the value itself; to change how a correct date looks, choose a different date format.

Can I use a different date style in Google Sheets than in Excel?
Yes. Google Sheets has its own menus and custom date-format dialog, so use Format > Number in Sheets rather than Excel’s ribbon paths. A spreadsheet downloaded or opened in Excel may display dates according to Excel’s formatting and regional settings, so review the file after moving it between programs.

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.