Data Import & Export

How to Fix MONTH Function Returning #VALUE! for Imported Dates

Emma BrownEmma Brown Nov 11, 2026 868 views

Question details

The user encounters a #VALUE! error when attempting to extract the month from an imported date using the MONTH function.

How to Fix the MONTH Function Returning #VALUE! Error for Imported Dates
Product
Spreadsheet
Device & OS
not provided
Scenario
Extracting month data from imported date values that follow a Month-Day-Year (MDY) format.
Observed behavior
The MONTH function returns a #VALUE! error because the imported dates are recognized as text strings rather than valid date serial numbers, often due to a mismatch in regional date settings.
Before you start

Ensure you have identified the entire column of imported dates that are causing the #VALUE! error so you can convert them all at once.

Solution 1Recommended

Convert Text to Valid Dates Using Text to Columns

Use the Text to Columns feature to parse the unrecognized text strings and convert them into a valid date format that your spreadsheet can process.

When importing data, dates such as 10/13/2024 might be stored as text if your computer's regional settings default to a different format, like Day-Month-Year (DMY). Because the spreadsheet doesn't recognize "13" as a valid month, it treats the entry as text.

The Text to Columns tool forces the application to read the text using the specific date format you define, converting it into a proper date serial number.

1
Select the data

Highlight the entire column containing the imported dates that are returning the error.

2
Open Text to Columns

Navigate to the 'Data' tab on the top ribbon and click on the 'Text to Columns' button to launch the conversion wizard.

3
Proceed to the final step

Keep the default 'Delimited' setting and click 'Next' twice to reach Step 3 of 3 in the wizard.

4
Set the Date format

Under the 'Column data format' section, select 'Date'. Then, choose 'MDY' (Month-Day-Year) from the adjacent drop-down menu.

5
Finish the conversion

Click 'Finish'. The text strings will be converted into real dates, and your MONTH formula will automatically calculate correctly.

Convert Text to Valid Dates Using Text to Columns
Error Fixed: Once the values become real date serial numbers, the MONTH function will instantly update and return the correct month number without the #VALUE! error.
Efficient Data Processing with WPS Office

Easily Clean Imported Data and Fix Date Errors in WPS Spreadsheet

WPS Spreadsheet provides intuitive, built-in data processing tools like Text to Columns and full formula support to quickly clean imported data and resolve common issues like the #VALUE! error.

  1. 1. Open your file: Launch WPS Spreadsheet and open the workbook containing the imported date text.
  2. 2. Select the dates: Highlight the column with the problematic dates.
  3. 3. Use Text to Columns: Go to Data > Text to Columns in the top ribbon menu.
  4. 4. Configure date settings: Navigate to the final step of the wizard, select 'Date', choose 'MDY', and click Finish.
  5. 5. Apply formula: Use the `=MONTH()` function to extract the month number effortlessly.
Free and lightweight alternative to Microsoft Excel.Fully compatible with Microsoft Excel (.xlsx) file formats and date formulas.Intuitive Text to Columns wizard for quick and accurate data conversion.Seamlessly handles different regional date formats to prevent formula calculation errors.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the MONTH function work on one computer but not another with the exact same file?

This happens due to differing regional settings on the computers. If one PC expects a MM/DD/YYYY format and the other expects DD/MM/YYYY, the second PC will treat a date like 10/13/2024 as invalid text. Consequently, the MONTH function cannot read it and returns a #VALUE! error.

Can I just change the cell format to 'Date' to fix the #VALUE! error?

No. Simply changing the cell's display format from 'General' or 'Text' to 'Date' does not alter the underlying data type. You must use a tool like Text to Columns or a function like DATEVALUE to re-parse the text into an actual numeric date serial number.

How do I visually know if my imported dates are stored as text?

By default in most spreadsheet software, text strings are aligned to the left side of a cell, whereas valid numeric values and date serial numbers are aligned to the right. If your dates align left automatically, they are likely stored as text.

What other spreadsheet functions are affected by imported text dates?

Any date-specific function will fail if it references a text string instead of a valid date. Functions like YEAR, DAY, DATEDIF, EDATE, and EOMONTH will all return a #VALUE! error until the text is converted into a date serial number.