Formatting Issues

How to Permanently Display Excel Dates as DD-MM-YYYY

Adam Davis Oct 9, 2026 871 views

Question details

The user needs to standardize the display format of imported dates in Excel to always show as DD-MM-YYYY, regardless of their original imported layout.

How to Permanently Display Excel Dates as DD-MM-YYYY

Product: Excel
Device & OS: not provided
Scenario: Importing date data from various external sources with mixed formats (e.g., YYYY-MM-DD, MM-DD-YYYY, MMM-DD-YYYY) and attempting to standardize their visual output.
Observed behavior: The imported dates appear in inconsistent formats depending on the source, and the user wants to enforce a consistent DD-MM-YYYY view across all date cells.

Before you start

Ensure that your imported date values are recognized as actual numerical dates in Excel rather than plain text strings. Custom formatting codes will only work on recognized date values.

Solution 1Recommended

Apply Custom Date Formatting

Use Excel's custom number formatting feature to force all selected date cells to display exactly as DD-MM-YYYY.

This is the most direct way to standardize date appearances without altering the underlying data. As long as Excel recognizes the cell contents as dates, this custom code will override any default regional display settings for those specific cells.

Select the Target Cells

Highlight the specific cells, rows, or columns that contain the dates you want to format.

Open Number Format Options

Navigate to the 'Home' tab on the ribbon. In the 'Number' group, click the dropdown menu and select 'More Number Formats'.

Apply Custom Code

In the Format Cells dialog box, select 'Custom' from the Category list. In the 'Type' field, enter DD-MM-YYYY and click OK.

Text Conversion Tip: If the format does not change after applying these steps, your dates are likely stored as text. Use the 'Text to Columns' feature under the Data tab to convert them to real dates first.

Solution 2

Adjust Regional Settings for Excel on the Web

Change your regional settings to properly interpret and display dates if you are using Excel for the web.

Smart Data Formatting

Easily Standardize Date Formats with WPS Spreadsheet

WPS Spreadsheet provides highly intuitive formatting tools to handle imported data and mixed date formats. It fully supports custom formatting codes and ensures seamless display consistency while remaining fully compatible with Excel files.

1. Open Your Document: Launch WPS Office and open the spreadsheet containing your imported dates.
2. Select Data: Highlight the specific cells or the entire column where you want to apply the new date format.
3. Access Format Cells: Right-click the highlighted area and select 'Format Cells', or simply press Ctrl+1 on your keyboard.
4. Apply Custom Formatting: Navigate to the 'Number' tab, click 'Custom', type 'DD-MM-YYYY' into the Type box, and click OK.

Easily apply custom formatting codes like DD-MM-YYYYSeamless and highly compatible with Microsoft Excel (.xlsx) formatsBuilt-in Text-to-Columns feature to quickly fix text-based imported datesFree, lightweight, and fast alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why won't my dates change format after I apply the custom DD-MM-YYYY code?

This happens when the spreadsheet reads your imported data as text strings instead of actual dates. To fix this, select the column, go to the Data tab, click 'Text to Columns', choose 'Delimited', click Next twice, and on the third step, select 'Date' to convert them into proper date values.

Does applying a custom date format change the underlying data?

No. Applying a custom number format like DD-MM-YYYY only alters how the date is visually displayed on the screen. The underlying serial number that the spreadsheet uses for calculations remains completely unchanged.

Can I set DD-MM-YYYY as the default date format for all new workbooks?

To change the default date format for all new files, you must change your computer's system-wide regional settings. On Windows, open the Control Panel, go to 'Clock and Region', select 'Region', and change the 'Short date' format to dd-MM-yyyy.

Document & File Issues