Fix Excel DATEVALUE #VALUE! Errors for DD/MM/YYYY Dates
Question details
The user needs to convert DD/MM/YYYY text dates into Excel dates when DATEVALUE returns a #VALUE! error.

- 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.
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.
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.
In Excel, select the column or range containing the DD/MM/YYYY text dates.
Go to Data > Text to Columns. Choose Delimited, click Next, clear any selected delimiters, and click Next again.
Under Column data format, select Date and choose DMY. Click Finish to convert the selected text into date values.
If needed, select the converted cells, press Ctrl+1, and choose a Date format to control how the dates appear.

Build an Excel Date with the DATE Function
Use a formula when the source text consistently follows the fixed-width DD/MM/YYYY pattern and you want to keep it unchanged.
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. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 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. 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. Format the results: Select the result cells and use Format Cells to choose a Date format.

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.




