Formula Errors

How to Fix Excel Date Formulas Returning Incorrect Results or a #NAME? Error

Maira Mehtab Sep 22, 2026 869 views

Question details

The user is experiencing incorrect results or `#NAME?` errors when using date-related functions like TEXT in Excel, caused by mismatched language or regional settings.

Product: Microsoft Excel
Device & OS: Windows
Scenario: Using date formulas such as TEXT to display formatted weekday names or specific date strings in an Excel workbook.
Observed behavior: Formulas return incorrect weekday names or evaluate to a `#NAME?` error instead of outputting the formatted date.

Before you start

Before changing your system settings, verify the expected locale or language of the workbook to ensure your new settings match the document's original environment.

Solution 1Recommended

Change Regional Settings in Excel for the Web and Desktop

Align the internal regional settings within the Excel application to match the workbook's formula language requirements.

Often, workbooks created in a different region will use formatting codes specific to that locale. Ensuring your Excel application settings match the file's original region resolves most formula recognition issues.

Adjust settings in Excel for the Web

Open your workbook in Excel for the Web, click on 'File' in the top ribbon, select 'Options', and then choose 'Regional Format Settings'. Choose the appropriate locale.

Adjust desktop application settings

In the Excel desktop application, go to 'File', then 'Options', and select 'Language'. Verify that your Office display language and authoring languages are correctly prioritized.

Recalculate formulas

After applying the changes, return to your worksheet and press F9 to recalculate the workbook, or double-click the cell containing the error and press Enter.

Settings Synced: Ensuring consistency between the web and desktop versions prevents the error from reappearing when switching platforms.

Solution 2

Modify Windows Date, Time, and Regional Formats

Update your computer's system-level date, time, and regional formats to be fully aligned with Excel's locale dependencies.

WPS Spreadsheet Solution

How to Easily Manage Date Formulas and Formats in WPS Office

WPS Office Spreadsheet provides a straightforward way to handle complex date functions and regional formatting natively. You can effortlessly adjust cell locales and use standard formulas like TEXT without needing to modify your entire computer's operating system settings.

1. Open the workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the problematic date formulas.
2. Access Format Cells: Select the cells returning the error, right-click, and choose 'Format Cells' from the context menu (or press Ctrl+1).
3. Adjust Locale directly: Under the 'Number' tab, click on 'Date'. Use the 'Locale (Location)' dropdown to set the specific region required for the formula, ensuring the display matches your expected text output.
4. Verify your TEXT formulas: Double-check your formula syntax to ensure it matches standard conventions (e.g., =TEXT(A2, "dddd")). The cell will update automatically.

Highly compatible with Microsoft Excel date formulas, including TEXT and regional formatting.Intuitive format cell options allow changing locales per cell without altering Windows OS settings.Built-in error checking highlights formula typos and format mismatches instantly.Lightweight, free to use, and opens .xlsx files seamlessly with zero data loss.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the TEXT function return a #NAME? error when formatting dates?

A #NAME? error usually occurs when the formula contains a typo in the function name, or if the formatting codes used inside the TEXT function (like "dddd" or "mmmm") are not recognized because your Excel or system language is set to a different region.

Do I need to change my entire Windows settings for just one Excel file?

Not necessarily. If only one workbook requires a specific format, you can often change the locale directly within Excel's 'Format Cells' menu under the Date category, rather than modifying your system-wide Windows Control Panel settings.

Will changing my Windows regional settings affect other applications?

Yes. Modifying the date, time, and regional formats in the Windows Control Panel applies system-wide. This means your web browsers, file explorer, and other software will also adopt the new date and time display formats.

Excel & Data Problems