Set Date Format in Google Sheets: A Practical Guide

To set a date format in Google Sheets, select the date cells and choose Format > Number > Date. For a specific pattern, choose Format > Number > Custom date and time. These options change how recognized dates look without changing the dates themselves. Before you start, make sure the cells contain actual date values rather than text that only looks like a date.

What You’ll Need

A Google account with access to the spreadsheet, and a web browser with the sheet open.
The cells or column you want to format. If you are unsure which cells contain dates, select a small sample first.
A clear idea of how you want dates displayed, such as 14/03/2026, Mar 14, 2026, or Saturday, March 14.

This guide covers Google Sheets in a web browser. Excel uses different menus and has some differences in custom date-format codes, so follow the Google Sheets paths below rather than Excel instructions.

Step-by-Step

Open the spreadsheet and select the cells. Click a single cell to format only that cell. To format a range, click and drag across the cells, or click the first cell and Shift-click the last. To format a whole column, click its letter at the top; to select several columns, drag across their letters. Applying a format to a whole column is convenient for an ongoing date-entry column, but select only the necessary area if other kinds of values share that column.
Apply the standard date format. With the cells selected, open Format > Number > Date. Sheets applies its standard date display for the spreadsheet’s locale. The same date may appear as 3/14/2026 in one locale and 14/03/2026 in another. The stored date is still the same date; this step changes its presentation.
Check that the result is right for your sheet. Look at a few cells, not only the first one. If dates are displayed in an unexpected order, check the spreadsheet locale and confirm that Sheets interpreted the entries as dates. If you need a different arrangement, use a custom format instead of repeatedly choosing the standard Date option.
Open the custom date and time options. Keep the target cells selected, then choose Format > Number > Custom date and time. A dialog opens with controls for building a format from date and time parts. Depending on the interface and available choices, you can select a suggested pattern or assemble one from the components shown. The preview helps you see how the pattern will look before you apply it.
Choose the parts and order you need. For a day-first date, use a pattern such as dd/MM/yyyy. For an abbreviated month, day, and four-digit year, use mmm d, yyyy, which can display as Mar 14, 2026. A pattern such as dddd, mmmm d can show the weekday and full month, for example Saturday, March 14. Use the dialog’s controls and preview to assemble or adjust the pattern, then click Apply.
Decide whether to include a year or time. A format such as mmm d leaves the year out of view, but the year remains part of the date value. If the cells contain times as well as dates, choose a format that includes the time components when you need to see them. A date-only display can hide a time value without removing it, which can matter for sorting or calculations.
Verify the range and reuse the format if needed. Check that the format was applied to every intended cell and that neighboring text or numbers were not included by mistake. To apply the same appearance elsewhere, select the other cells and repeat the menu steps. You can also copy a correctly formatted cell and use Edit > Paste special > Format only on the destination range; this copies the formatting without replacing the destination’s values.

Google Sheets stores recognized dates as date values that can be used in sorting and date calculations. The format is a display instruction: for example, changing a date from 2026-03-14 to Mar 14, 2026 does not change its underlying value. This is why choosing a format cannot repair a cell that contains plain text instead of a date.

Common Mistakes

Formatting text instead of dates. A cell can contain the characters 14/03/2026 without Sheets recognizing them as a date. Applying a date format to text usually has no visible effect. This often happens after pasting data from a website, importing a CSV, or entering a date in a format Sheets does not recognize.
Mixing up month and day. Dates such as 04/05/2026 are ambiguous: they could mean April 5 or May 4. The spreadsheet’s locale affects how Sheets interprets many entered dates. Confirm the intended meaning before changing formats, especially in shared or imported data.
Expecting a format to change the underlying date. A custom display such as mmmm shows only the month name; it does not turn a date into a different value or remove the day and year. If you need to alter the data itself, that requires editing or transforming the values, not just formatting cells.
Selecting only the cells currently filled in. New entries below a formatted range may use a different format. If the column is dedicated to dates, format the whole column or a suitably large range so future entries follow the same display.
Confusing a date format with text output. A formula or import may produce text that looks like a date. Number formatting works on date values, not on every text string that resembles one. Check the source of the value before assuming the display menu is broken.

Tips

Pick one unambiguous style for shared sheets. In a team or across regions, a month-name format such as 14 Mar 2026 makes the month/day order clear. A numeric pattern like 03/04/2026 can be read differently by different people.
Use a four-digit year when the year matters. Formats with yy save space, but two-digit years are less clear in long-lived records. A visible four-digit year makes reports, schedules, and archived data easier to interpret.
Format the input column before adding more data. Applying a date display to a blank date column can help keep later entries consistent. Still enter valid dates; formatting a blank cell does not guarantee that every future text entry will be converted automatically.
Keep full dates for sorting and filtering. Showing only a month or weekday can make different dates look identical. Retain the complete date value and use the shorter format only when the surrounding context makes the omitted information clear.
Use the preview in the custom-format dialog. It is a quick way to catch a reversed order or an unwanted time component before you apply the pattern to a large range. If a format is hard to read, test it on a few representative dates first.
Check locale before sharing or importing data. To review it, open File > Settings and look at Locale under the General settings. The locale is a spreadsheet-wide setting and can affect default date, number, and currency conventions, as well as how new entries are interpreted. Coordinate a change with collaborators instead of switching it casually.

Troubleshooting

The date does not change after you apply a format. The value may be text. Click the cell and inspect its contents in the formula bar. If it is a text string, try re-entering the date in a format recognized by the spreadsheet’s locale, or correct the source data and import it as dates. If the source uses a different regional order, confirm what each date means before converting it; automatically swapping month and day can corrupt the data.

The date changes to an unexpected day or month. First check whether the original entry was ambiguous, such as 06/07/2026. Then review File > Settings > Locale. A display format can show a recognized date in a different order, but it cannot tell you whether an ambiguous source entry was intended to mean June 7 or July 6. Verify against the original record before making a correction.

You see a number instead of a date. Dates are stored as serial values, so a cell can show a number if it is set to a number format. Select the cell and choose Format > Number > Date, or apply a custom date format. If the value is an ordinary number rather than a date serial, changing its format will not make it the intended date; check how the value was entered or imported.

Only some cells format correctly. The range may contain a mixture of real dates, text, and blank cells. Test representative cells individually. You may need to standardize the source values first, then select the complete range and apply the format again. Also check that the selection did not stop partway down the column.

A time seems to disappear. A date-only pattern hides the time portion of a date-time value, but does not necessarily remove it. Open the custom date and time dialog and add a time component if it needs to be visible. If the time should actually be removed from the data, edit or transform the values separately rather than relying on a display format.

Frequently Asked Questions

How do I change the date format for an entire column?
Select the column by clicking its letter, then choose Format > Number > Date or open Format > Number > Custom date and time. This also applies the display format to blank cells in the column, so it is useful when more dates will be added later.

Can I use a date format like DD-MM-YYYY?
Yes. Select the cells and open Format > Number > Custom date and time. Use the dialog to build a day, month, and four-digit year pattern with the separators you want, then check its preview and click Apply. The result can vary in appearance according to the selected components and locale.

Will changing the format change the date value?
No. A date format changes how a recognized date is displayed, not the underlying value. The same value can be shown as a numeric date, an abbreviated month, or a full weekday-and-date style and still be used as a date in the sheet.

Why does a date format not work on dates I pasted?
Pasted values may have arrived as text, especially when copied from a website or imported from another system. Check whether Sheets recognizes the cell as a date. If it does not, correct or convert the source values first; choosing a display format alone cannot turn arbitrary text into a date.

Can I make Google Sheets use a different default date order?
You can change the spreadsheet’s locale in File > Settings, under the General settings. This setting applies to the spreadsheet rather than just one selected range and may affect date interpretation and other regional conventions. Consider collaborators and existing entries before changing it.

Can I show only the month or weekday?
Yes. Use Format > Number > Custom date and time and choose only the parts you want displayed. The underlying value remains a full date, so two different dates can appear identical if the format hides the day or year. Keep that in mind when reading or sorting the data.

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.