How to Fix MONTH Function Returning #VALUE! for Imported Dates
Question details
The user encounters a #VALUE! error when attempting to extract the month from an imported date using the MONTH function.

- 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.
Ensure you have identified the entire column of imported dates that are causing the #VALUE! error so you can convert them all at once.
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.
Highlight the entire column containing the imported dates that are returning the error.
Navigate to the 'Data' tab on the top ribbon and click on the 'Text to Columns' button to launch the conversion wizard.
Keep the default 'Delimited' setting and click 'Next' twice to reach Step 3 of 3 in the wizard.
Under the 'Column data format' section, select 'Date'. Then, choose 'MDY' (Month-Day-Year) from the adjacent drop-down menu.
Click 'Finish'. The text strings will be converted into real dates, and your MONTH formula will automatically calculate correctly.

Use the DATEVALUE Function to Convert Text Dates
Wrap your date text in a DATEVALUE function to convert it into a date serial number that the MONTH function can read.
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. Open your file: Launch WPS Spreadsheet and open the workbook containing the imported date text.
- 2. Select the dates: Highlight the column with the problematic dates.
- 3. Use Text to Columns: Go to Data > Text to Columns in the top ribbon menu.
- 4. Configure date settings: Navigate to the final step of the wizard, select 'Date', choose 'MDY', and click Finish.
- 5. Apply formula: Use the `=MONTH()` function to extract the month number effortlessly.

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.




