Excel Error Codes

Fix Excel DATEVALUE #VALUE! Errors for DD/MM/YYYY Dates

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 870 views

Question details

The user needs to convert DD/MM/YYYY text dates into Excel dates when DATEVALUE returns a #VALUE! error.

Fix Excel DATEVALUE #VALUE! Errors for DD/MM/YYYY Dates
Product
Microsoft Excel
Device & OS
not provided
Scenario
A spreadsheet contains text dates such as 24/09/2022, but the regional date format may expect the month first.
Observed behavior
DATEVALUE returns #VALUE! when Excel cannot interpret the text as a valid date under the current regional settings.
Before you start

Check a sample cell with =ISTEXT(B3) to confirm whether it contains text. If you use Windows, check the short-date format in Control Panel > Region; a month-first setting can prevent Excel from recognizing day-first text.

Solution 1Recommended

Convert DD/MM/YYYY Text with Excel Text to Columns

Use this method to convert a column of day-first text dates without relying on the system's default date order.

1
Select the dates

In Excel, select the column or range containing the DD/MM/YYYY text dates.

2
Open the conversion wizard

Go to Data > Text to Columns. Choose Delimited, click Next, clear any selected delimiters, and click Next again.

3
Set the date order

Under Column data format, select Date and choose DMY. Click Finish to convert the selected text into date values.

4
Format the result

If needed, select the converted cells, press Ctrl+1, and choose a Date format to control how the dates appear.

Convert DD/MM/YYYY Text with Excel Text to Columns
Use the converted dates directly: The converted cells are date values, so formulas can reference them directly instead of applying DATEVALUE to them.
Convert dates in WPS Spreadsheets

Convert DD/MM/YYYY Text Dates in WPS Spreadsheets

Use a DATE formula in WPS Spreadsheets to turn fixed-width day-first text into date values without depending on DATEVALUE's regional interpretation.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Enter the formula: If the text date is in B3, select an empty cell and enter =DATE(RIGHT(B3,4),MID(B3,4,2),LEFT(B3,2)).
  3. 3. Convert the remaining rows: Press Enter and drag the result cell's fill handle down to apply the formula to other fixed-width DD/MM/YYYY dates.
  4. 4. Format the results: Select the result cells and use Format Cells to choose a Date format.
Convert DD/MM/YYYY text by specifying the day, month, and year explicitlyOpen and edit Microsoft Excel XLSX and XLS filesApply the formula to multiple rows with the fill handleUse familiar spreadsheet formulas and date formatting tools
microsoft office alternative - wps office

Frequently Asked Questions

Why does DATEVALUE return #VALUE! for 24/09/2022?

If Excel expects MM/DD/YYYY, it may interpret 24 as the month, which is invalid. Use Data > Text to Columns with the DMY setting or build the date with the DATE function.

How can I tell whether a date is stored as text?

Enter =ISTEXT(B3) in an empty cell. TRUE means B3 contains text; FALSE means it contains another type of value.

Why does my converted date appear as a number?

Excel stores dates as serial numbers. Select the cell, press Ctrl+1, and choose a Date format to display it as a date.

Will the DATE formula work with dates such as 5/9/2022?

The fixed-position formula shown here expects two-digit days and months, such as 05/09/2022. Use Data > Text to Columns with DMY or standardize the text before applying that formula.

Where can I check the Windows short-date format?

Open Control Panel > Region and look at the short-date format. If it is month-first, day-first text may not be recognized by DATEVALUE.