Excel Error Codes

How to Fix Excel DATEVALUE Error with Different Date Formats

Emma Brown Oct 12, 2026 870 views

Question details

The user needs to resolve a #VALUE! error that occurs when Excel's DATEVALUE function tries to convert text dates due to conflicting regional date formats.

How to Fix Excel DATEVALUE Errors with Different Date Formats

Product: Excel
Device & OS: not provided
Scenario: Converting text-formatted dates into serial date values across different regional settings.
Observed behavior: Excel returns a #VALUE! error instead of a valid date number because the text date format (e.g., MM/DD/YYYY) does not match the system's regional date configuration (e.g., DD-MM-YYYY).

Before you start

Check your computer's regional date settings in the Control Panel or System Settings to confirm your default date format before applying conversions in Excel.

Solution 1Recommended

Extract Date Components Using a Formula

Use a combination of DATE, RIGHT, LEFT, and MID functions to explicitly construct a valid date, bypassing system format dependencies.

This method forces Excel to build a date using specific pieces of the text string, avoiding reliance on regional system settings.

Select the target cell

Select an empty cell next to your text date (for example, cell B1 if your problematic text date is in A1).

Enter the DATE formula

Type the formula =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)) to extract the year, month, and day respectively.

Apply and fill down

Press Enter to apply the formula. Once the correct date format appears, drag the fill handle in the bottom-right corner of the cell down to apply the formula to the rest of your data.

Formula Customization: This exact formula assumes a strict MM/DD/YYYY format with leading zeros (e.g., 01/15/2023). If your days or months lack leading zeros, you will need to adjust the extraction lengths or use an alternative method.

Solution 2

Use Text to Columns for Quick Conversion

Utilize the Text to Columns wizard to force Excel to recognize the specific incoming date format.

Solution 3

Specify Source Locale in Power Query

Ideal for large or recurring data imports where you need to interpret the date format correctly from the source.

Resolve Date Errors in WPS Office

Seamlessly Handle Complex Date Formats with WPS Spreadsheet

WPS Office provides highly compatible data processing tools similar to Excel, including powerful Text to Columns and comprehensive Date functions to seamlessly fix formatting mismatch issues.

1. Open WPS Spreadsheet: Launch WPS Office and open the document containing your incorrectly formatted dates.
2. Access Text to Columns: Select the problematic column, go to the 'Data' tab, and click on 'Text to Columns'.
3. Format as Date: Follow the wizard to the final step, select the 'Date' option, and choose the correct MDY format to instantly resolve the formatting mismatch.

Free, lightweight, and fast installationFully compatible with Microsoft Excel (.xlsx) formats and formulasFamiliar user interface with no learning curve requiredBuilt-in intelligent data conversion tools to fix date errors instantly

microsoft office alternative - wps office

Frequently Asked Questions

Why does DATEVALUE return a #VALUE! error?

The #VALUE! error typically happens because the text representation of the date does not match the default date format specified in your computer's regional OS settings. Excel cannot interpret which numbers represent the month or day, resulting in a calculation error.

Can I change my system's regional settings to fix this instead of using formulas?

Yes. You can fix this by changing your Windows Regional Settings to match the format of your dates (e.g., changing from DD-MM-YYYY to MM/DD/YYYY). However, be aware that this changes the default date format across all applications on your computer.

What if my dates do not have leading zeros (e.g., 1/2/2023)?

If your text dates have variable lengths, a static RIGHT/LEFT/MID formula might extract the wrong numbers. In this case, using the Text to Columns method or Power Query locale adjustment is much more reliable, as they automatically handle variable character lengths.

Microsoft Office Error Codes