How to Edit Date Format in Google Sheets: Complete Guide

To edit a date’s appearance in Google Sheets, select the date cells, then choose Format > Number and pick a date style. For a specific layout, choose Format > Number > Custom date and time, arrange the date components, and click Apply. This changes how a date is displayed, not the underlying date value. The instructions below are for Google Sheets in a web browser; Excel uses different menus.

What You’ll Need

A Google account with access to the spreadsheet. You need edit permission to change its formatting.
A browser and the spreadsheet open in Google Sheets.
The cells containing dates, or a clear idea of the display style you want, such as 14/06/2026, Jun 14, 2026, or 2026-06-14.

If you are working with a shared sheet, consider asking whether other people depend on the current date display. Formatting usually changes the view for everyone who opens the file.

Step-by-Step

Open the spreadsheet and identify the date cells. Click the tab containing the dates. Select the cell or range you want to change. To format a whole column, click its letter at the top; to format several separate cells, hold Ctrl on Windows or ⌘ on Mac while selecting them. Avoid selecting unrelated numbers if you do not want those cells to receive date formatting too.
Check whether the entries are actual dates. Click one of the cells and look at the formula bar above the grid. A recognized date generally appears as a date value there, even if the cell shows a different style. You can also test a quick format change on one cell before applying it to a large range. If the cell contains plain text that only looks like a date, number formatting may not change its appearance; see Troubleshooting below.
Open the number-format menu. With the cells selected, click Format in the top menu, point to Number, and choose a suitable option under the date formats. Depending on your sheet and locale, options can include a short date, a long date, or a date and time. The menu may show different examples from those in this guide, because Google Sheets can adapt formats to the spreadsheet’s locale.
Choose a built-in date style. Select the option that most closely matches what you need. For example, a short date is compact for a transaction log, while a long date that spells out the month can make a report easier to scan. Look at the selected cells after applying the option. If the day and month appear in the wrong order, or the style does not include the year, choose a different option or create a custom one.
Open the custom date controls if you need a precise layout. Select the cells again if necessary, then go to Format > Number > Custom date and time. A dialog opens with a preview and controls for date and time components. In the dialog, you can choose a suggested arrangement or build one by adding, removing, and rearranging components such as day, month, year, hour, or minute. The controls may look slightly different as Google updates its interface, but the custom date and time option is in the Number menu.
Set the order and detail you want. Use the component controls to decide whether the day or month comes first, whether the month is written as a number or a name, and whether the year uses two or four digits. For an unambiguous report, a four-digit year is usually clearer. Check the preview: a style such as day, abbreviated month, and four-digit year will look like 14 Jun 2026, while a numeric year-month-day style can look like 2026-06-14. Make sure the preview suits the audience who will read the sheet.
Apply the custom style and verify more than one cell. Click Apply in the dialog. Check a date near the beginning and another near the end of the selected range, especially if the cells include different years or dates with times. If some cells do not change, they may contain text, have a different value type, or have been left out of the selection. You can reopen the custom date and time dialog to adjust the style without changing the dates themselves.
Use the toolbar as a quick alternative when appropriate. Google Sheets may show number-format shortcuts in the toolbar, including a date-format control. Select the date cells and use a visible date shortcut if it gives the result you want. For more choices, return to Format > Number; the menu is the dependable place to find built-in and custom formats. If you are unsure what a shortcut will do, try it on a single cell first.

A useful distinction is that the cell’s value and its display are separate. A date can be displayed as 6/14/2026, 14 Jun 2026, or another style while still representing the same date. Changing the format does not move the date forward or backward, and it does not rewrite the source data in the way that editing the cell would.

Common Mistakes

Expecting formatting to repair text. If an entry was imported as text, choosing a date style may leave it unchanged. First confirm that Sheets recognizes the entry as a date. A format controls display; it does not reliably convert every text string into a date value.
Confusing month and day order. The value 04/06/2026 can mean April 6 or 4 June, depending on the intended convention and locale. Do not guess from the display alone. Confirm the source data and use a style that makes the order clear, such as a month name or a four-digit year-month-day arrangement.
Formatting an entire sheet unnecessarily. Selecting all cells and choosing a date format can make ordinary numbers display as dates. Select only the relevant range or column, and check the result before repeating the change across the sheet.
Assuming everyone sees dates the same way. A spreadsheet’s locale influences default date formats and how entries are interpreted. A collaborator may see a different familiar convention, so use a clear format when a file is shared across regions.
Including unwanted time components. A date-time value can contain a time even if the cell previously showed only a date. If a custom style adds hours or minutes, remove those components in the dialog. Conversely, if you need to show the time, choose a date-and-time style rather than expecting a date-only format to display it.

Tips

Prefer readable dates in shared reports. Writing the month as a word can prevent ambiguity between day-first and month-first conventions. For example, 14 Jun 2026 is easier to interpret internationally than 06/14/26.
Apply one consistent style to a date column. Select the column or the populated range and format it once. This makes sorting, scanning, and comparing dates easier. If new rows are added later, check whether the formatting carries into them; if not, extend the formatted range.
Keep the year when dates span multiple years. A display that omits the year can be fine for a short schedule, but it can cause confusion in a long-term tracker. Use a four-digit year for records that may be reviewed later.
Use the spreadsheet locale deliberately. To review it, open File > Settings and check the Locale setting. Changing the locale can affect default date, number, and currency conventions and may affect how entered values are interpreted. Do not change it casually in a shared workbook; agree on the desired convention first.
Format a copy when the source must remain untouched. Duplicate the sheet or copy the data to a new tab before making broad changes. This is especially useful when an imported report has mixed date styles and you need to compare the original with a cleaned-up presentation.
Remember that formatting is not the same as sorting. A date displayed as text-like characters can still sort incorrectly if the cells are text. Once entries are recognized as date values, sorting them chronologically works as expected; changing the display alone cannot make text sort as real dates.

Troubleshooting

The date format menu does not change a cell. The entry may be plain text rather than a date value. Check whether the cell is left-aligned or whether its value came from an import, but do not rely on alignment alone as proof. Try editing one sample cell and entering the date again in a form that matches the sheet’s locale. If Sheets recognizes it, apply the desired format. For imported data, check the source file’s date convention before converting anything; a day/month reversal can silently produce the wrong date.

The date appears as a number. Sheets stores recognized dates as numeric values internally, so a cell set to a general number format can show a number instead of a familiar date. Select it and choose Format > Number, then select a date style. If the result is still not the expected date, verify that the underlying value is correct and that you selected the correct cell.

The date shows #### or does not fit. The column may be too narrow for the chosen style. Drag the boundary between the column letters to widen it, or use a shorter date format. The hash marks usually indicate a display-width issue, not a damaged date.

The date changes when you enter it. Check File > Settings > Locale and compare the locale with the order used in your entry. For example, an ambiguous numeric date can be interpreted differently in a month-first locale than in a day-first locale. If the workbook is shared, coordinate before changing the locale because it can affect conventions elsewhere in the file.

Some cells show the style and others do not. Recheck the selected range and inspect the cells that failed. They may contain text, blanks, formulas that return text, or values outside the selection. Apply the format to the correct range only after confirming that the entries are dates. If a formula produces a date as text, the formatting menu cannot change the text into a date value by itself; you may need to adjust the formula or clean the source data.

A date appears one day earlier or later. Check whether the cell contains a time as well as a date, and whether the source data or spreadsheet time-zone settings are involved. Open File > Settings to review the time zone. Be cautious when changing it in a shared sheet, since time-based values may display differently. First compare the cell’s underlying value and source before altering settings.

Frequently Asked Questions

Can I change the date format without changing the date itself?
Yes. Select the date cells and choose another option under Format > Number. This changes the display style, not the represented date, provided the cell contains a recognized date value.

How do I make Google Sheets show day/month/year?
Select the cells and open Format > Number > Custom date and time. Arrange the day, month, and year components in that order, check the preview, and click Apply. A built-in option may also provide the order you need.

Can I use a month name instead of a number?
Yes. In the custom date and time dialog, choose a month component that displays the month as a name, then select the abbreviated or full style available in the controls. The preview shows the result before you apply it.

Why does Google Sheets interpret my date incorrectly?
The spreadsheet locale and the date’s entry order may not match. Check File > Settings > Locale, then confirm what the original date means before changing data. A date with a written month, such as 14 Jun 2026, is less ambiguous than a numeric date.

Will other people see my chosen date format?
Usually, yes: number formatting is part of the spreadsheet, so collaborators viewing the same cells generally see that formatting. Their locale can affect some default conventions, so use a clear custom style and check the shared sheet if consistent presentation matters.

Can I format dates in the Google Sheets mobile app?
The app includes formatting controls, but menu names and layout can vary by device and app version. These steps describe the browser interface. For the most precise custom date layout, use Google Sheets in a browser and open the custom date and time dialog.

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.