Google Sheets Date Format dd/mm/yyyy: Formula and Steps

To show dates as dd/mm/yyyy in Google Sheets, select the date cells and choose Format > Number > Custom date and time, then apply a day/month/year pattern. If you specifically need a formula that returns the date as text, use =TEXT(A2,"dd/mm/yyyy") when A2 contains a real date.

Quick answer: format the cells, not the date values

For most sheets, applying a number format is the right solution. It changes how a date is displayed without replacing the underlying date value. The cell remains usable for date arithmetic, sorting, filtering, and charts. A formula is useful when you need a text string—for example, to place a formatted date inside a sentence or export a label—but it is usually not the best way to format an ordinary date column.

To set the display format:

Select the cells or entire column containing the dates.
Open Format > Number > Custom date and time.
Choose or create a pattern with a two-digit day, two-digit month, and four-digit year. Confirm that the order is day, month, year, then click Apply.

A date such as 31 March 2026 will then display as 31/03/2026. If the cells contain text that only looks like dates, changing their number format may do nothing; the values must first be recognized as dates.

What dd/mm/yyyy means in Google Sheets

In a custom date pattern, dd requests a two-digit day, mm requests a two-digit month, and yyyy requests a four-digit year. The double letters add leading zeroes where needed: 5 April 2026 is shown as 05/04/2026, rather than 5/4/26. This is a display convention, not a different kind of date.

Google Sheets stores recognized dates as numeric date values. Their display depends on the cell’s number format and, for defaults and interpretation, the spreadsheet’s locale. That distinction explains why a cell can show 05/04/2026 in one format and Apr 5, 2026 in another while still representing the same date. Changing the format does not move the date or change its day, month, or year.

The format is especially useful where readers expect day first, such as in many countries outside the United States. It also avoids a two-digit-year ambiguity. However, a slash-separated value can still be misread if someone assumes month first. For reports shared across regions, state the order in a heading or use an unambiguous written month, such as 5 Apr 2026.

Using the TEXT formula

Use the TEXT function when the result needs to be text rather than a formatted date value. With a true date in cell A2, enter this in another cell:

=TEXT(A2,"dd/mm/yyyy")

If A2 contains 31 March 2026, the formula returns the text 31/03/2026. Copy or fill the formula down to format other rows, adjusting the cell reference as needed. The pattern inside the quotation marks controls the displayed text, so changing it to dd-mmm-yyyy would produce a result such as 31-Mar-2026.

Because the formula returns text, the result is not as convenient as a date value for calculations. For example, date subtraction should use the original date cells, not the strings produced by TEXT. Text output can also sort alphabetically rather than chronologically in some arrangements. Keep the original date column for calculations and use the formula column only where a text representation is needed, such as a message, filename component, or fixed-format export.

Google Sheets uses the same basic TEXT approach as Excel, though spreadsheet locale settings can affect formula separators in some regions. The formula above uses commas, as requested by the common English-language syntax. If Sheets reports a formula parse error, check the spreadsheet locale and whether its formula syntax expects semicolons instead of commas.

Cell formatting versus a formula

Choose cell formatting when people will work with the dates as dates. Choose TEXT when you need the formatted characters themselves. This difference matters even if both options look identical on screen.

Cell format: retains a date value, so date calculations, chronological sorting, and date-based filters continue to work naturally.
TEXT formula: returns a string. It is useful for presentation or text assembly, but may need conversion before it can be used in date calculations.
Plain text entry: can preserve exactly what was typed, but Sheets may not recognize it as a date and therefore cannot reliably calculate with it.

For example, a column of event dates should normally contain date values with a custom display format. A sentence such as “The appointment is on 31/03/2026” may use a formula-generated text date if the sentence itself is being assembled from cells. Avoid converting an entire working date column to text merely to make the cells look consistent.

Locale and day-first date interpretation

Formatting and interpretation are related but separate. A custom pattern determines how a recognized date appears. The spreadsheet’s locale influences default date conventions and how entered or imported date strings are interpreted. To review it, open File > Settings and check the Locale setting. Google Sheets applies that setting to the spreadsheet, so changing it can affect default date, number, and currency behavior for the file.

Consider the text 04/05/2026. It could mean 4 May or April 5, depending on the convention used when it was entered. Applying dd/mm/yyyy after Sheets has already interpreted it cannot correct a value that was parsed as the wrong date; it only changes the display of that interpreted value. When importing data from another country or system, confirm the source order before converting or formatting the column.

If collaborators use different regional settings, agree on an input convention and test a few sample rows before processing a large import. Dates written with a spelled-out month, such as 5 Apr 2026, are often easier for people to interpret than ambiguous numeric strings. For automated data exchange, use a documented date standard and check how the receiving system expects dates.

When Sheets does not recognize the values as dates

A common clue is that applying the custom format leaves the cell unchanged, or the value aligns and behaves like ordinary text. A pasted value can look like a date while still being stored as text, particularly when the source has unusual separators, extra spaces, or an order that conflicts with the sheet locale.

Use this troubleshooting sequence:

Select a problem cell and inspect its contents in the formula bar. Look for leading apostrophes, spaces, or unexpected characters.
Check the spreadsheet locale under File > Settings and compare it with the date order in the source data.
Try entering a known date directly in a blank cell using the sheet’s expected input convention, then apply the custom format to confirm the display pattern works.
For imported data, preserve a copy of the original column before changing or converting values. Check several dates where day and month could be confused, not just values with a day greater than 12.

Do not assume that a visible slash proves a cell is a date. A literal string such as 31/03/2026 may be text, while a date value may be displayed without slashes. The cell’s behavior—whether it can be used in date calculations and responds to number formatting—is more informative than its appearance alone.

Applying the format to new and existing rows

To update a whole existing column, select the column letter before using the custom date-and-time menu. This applies the display style to current date values and usually to dates entered into those cells later. If the column also contains headings or unrelated values, select only the relevant range instead, so the format does not create confusing results elsewhere.

For a new table, format the intended date column before entering or pasting dates. This helps keep the presentation consistent, but it does not guarantee that every pasted string will be parsed correctly; check imported data after pasting. When copying from another spreadsheet, the source may bring its own formatting, so reapply the desired pattern to the destination range if the display changes.

For a formula-generated text date, place the formula beside the original date column and fill it down. Keep the original values if users may need to calculate elapsed days, group dates by month, or sort the data chronologically. A clean layout often labels the columns clearly, for example “Date (value)” and “Date (text for export),” so later users know which one to use in calculations.

Google Sheets and Excel differences

The date-pattern idea is familiar in both programs, and the TEXT function is available in Google Sheets and Excel. The menu paths and locale controls are not identical, however. In Google Sheets in a browser, use Format > Number > Custom date and time; do not follow Excel ribbon instructions for a Sheets file. Excel has its own number-format dialog and regional settings, so instructions should match the application being used.

When a workbook is shared or converted between Sheets and Excel, check the display and the underlying values after conversion. A custom format may render differently if a pattern or locale is interpreted differently, and text returned by TEXT remains text in either program. If a file is intended for both, test representative dates—including dates early in the month and dates that could be confused between day-first and month-first conventions.

Frequently Asked Questions

What formula changes a Google Sheets date to dd/mm/yyyy?
With a real date in A2, use =TEXT(A2,"dd/mm/yyyy"). It returns a text string such as 05/04/2026 for 5 April 2026. If you only need to change how the cell looks, apply a custom date format instead.

How do I change the date format without a formula?
Select the date cells, then choose Format > Number > Custom date and time. Set the order to day, month, year, with two digits for day and month and four for year, then apply the format.

Why does dd/mm/yyyy not change my cell?
The cell may contain text rather than a recognized date, or the value may have been interpreted using a different locale. Confirm the source’s date order and inspect the cell’s contents before changing the data. Formatting changes appearance; it does not reliably convert arbitrary text.

Does TEXT keep the result as a date?
No. TEXT returns text formatted according to the pattern. Retain the original date value for calculations, filters, and chronological sorting, and use the text result only where a string is required.

How can I make Sheets interpret entered dates as day/month/year?
Review File > Settings > Locale and select the locale appropriate for the spreadsheet. Then test the entry convention with unambiguous dates before importing a full dataset, because locale affects interpretation as well as default formatting.

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.