Data Import & Export

How to Identify Invalid Dates and Convert to UK DMY Format in Excel

Aamir Naveed Akram Oct 10, 2026 869 views

Question details

The user needs to identify invalid date entries in Excel and convert valid dates to the UK day-month-year (dd/mm/yyyy) format.

How to Identify Invalid Dates and Convert to UK DMY Format in Excel

Product: Excel
Device & OS: not provided
Scenario: Cleaning and formatting imported data that contains date anomalies such as out-of-range days and pre-1900 historical dates.
Observed behavior: Certain imported dates appear as invalid entries (e.g., 12/32/1997) or are strictly treated as text (dates before 1900), preventing standard formatting tools from applying the UK DMY format.

Before you start

Select your date column and copy it into a new sheet before proceeding, as converting mixed text and invalid date formats may permanently alter your original data strings.

Solution 1Recommended

Validate and Convert Valid Dates Using Custom Formatting

Use Excel's custom formatting to apply the UK DMY layout to valid dates while keeping invalid or historical dates as text.

Excel uses a 1900 date system, meaning it cannot natively process dates prior to January 1, 1900. Dates like 03/14/1875 should be intentionally retained and processed as text.

For standard dates, applying a custom cell format is the most reliable way to force the display into a day-month-year structure.

Select the valid dates

Highlight the cells containing the valid dates you want to convert to the UK format.

Open Format Cells

Right-click the selected area and choose 'Format Cells', or press Ctrl+1 on your keyboard.

Select Custom format

Navigate to the 'Number' tab, and click on 'Custom' in the Category list on the left.

Apply DMY structure

In the 'Type' input box, enter 'dd/mm/yyyy' and click OK to apply the formatting.

Validate and Convert Valid Dates Using Custom Formatting

Handling Pre-1900 Dates: Any dates entered prior to the year 1900 will not change format. Excel treats these as text strings rather than standard serial numbers.

Solution 2

Parse Mixed Date Formats Using Text to Columns

Use the Text to Columns wizard if your valid dates are currently stored as text and refuse to change format.

Clean and Format Date Data Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive tools for identifying invalid data and converting your dates into any regional format, including UK DMY. It handles complex data cleaning tasks effortlessly.

1. Open your data: Launch WPS Spreadsheet and open your workbook containing the dates.
2. Select the date column: Highlight the cells or the entire column that contains your date data.
3. Access cell formatting: Right-click the selection and choose 'Format Cells', then navigate to 'Custom'.
4. Apply the UK format: Enter 'dd/mm/yyyy' in the Type field and click OK to instantly convert valid dates.

Fully compatible with Microsoft Excel (.xlsx) date formats and serial number systems.Built-in Text to Columns and Data Validation features for easy date conversion.Comprehensive custom formatting options to display dates precisely as dd/mm/yyyy.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel not recognize dates before 1900?

Excel operates on a 1900 date system, where January 1, 1900, is represented by the serial number 1. Because it cannot generate serial numbers for dates prior to this, any historical date (like 1875) is treated simply as text.

How can I easily find invalid dates like 12/32/1997 in my spreadsheet?

You can use the ISERROR and DATEVALUE functions. By entering =ISERROR(DATEVALUE(A2)) in an adjacent column, Excel will return TRUE if the date is invalid or formatted in a way it cannot interpret.

Can I calculate differences between pre-1900 dates in Excel?

Standard date math (like subtracting one cell from another) will result in an error because pre-1900 dates are stored as text. You would need to use complex formulas to manually extract and calculate the year, month, and day separately.

Excel & Data Problems