How to Fix Excel Date Formula Returning JAN-00 or #VALUE! Error
Question details
The user's Excel date formulas are returning an invalid 'JAN-00' representation or a '#VALUE!' error instead of the correct date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Performing date calculations or referencing date cells where the source data might be stored as text or evaluating to zero.
- Observed behavior
- The formula outputs 'JAN-00' or throws a '#VALUE!' error. Modifying the cell's number format to 'Date' does not resolve the underlying value issue.
Ensure that the source cells referenced in your formula do not contain hidden spaces or text formatting that prevent Excel from recognizing them as numeric date serials.
Convert Text Dates to Valid Serial Numbers Using the DATE Function
Use the DATE, YEAR, MONTH, and DAY functions to reconstruct a valid Excel date from problematic text entries that cause #VALUE! errors.
Often, imported data looks like a date but is actually stored as text. When formulas attempt to calculate these text strings, Excel returns a #VALUE! error. Using the DATE function forces Excel to extract the components and build a proper date serial number.
Click on an empty cell where you want the corrected, valid date to appear (for example, B1, assuming your broken date text is in A1).
Type the formula =DATE(YEAR(A1), MONTH(A1), DAY(A1)) into the formula bar.
Press Enter. This extracts the year, month, and day components from the text cell and combines them into a normalized Excel date serial number.
Right-click the new cell, select "Format Cells," navigate to the "Number" tab, choose "Date," and select your preferred date format to display it correctly.
Verify if Source Cells Contain Real Dates Using ISNUMBER
Check whether Excel is treating your source data as text or a valid number before performing date calculations to prevent errors.
Fix Formula Errors Seamlessly with WPS Office
WPS Spreadsheet offers powerful date and time functions fully compatible with Microsoft Excel, helping you easily identify, correct, and format data without encountering frustrating text-to-date #VALUE! errors.
- 1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the document containing the date formula errors.
- 2. Use the Error Checking tool: Look for a small green triangle in the corner of your cell. Click the warning icon to see if WPS suggests "Convert to Number" to instantly fix text-based dates.
- 3. Apply date formulas easily: If manual correction is needed, you can use =DATE(YEAR(A1), MONTH(A1), DAY(A1)) just like in Excel to construct valid dates.
Frequently Asked Questions
Why does my Excel date show as JAN-00?
Excel calculates dates using serial numbers starting from January 1, 1900 (serial number 1). If your formula returns a 0 due to an empty cell reference or an error evaluating text, formatting that 0 as a date results in 'JAN-00', which represents January 0, 1900.
How can I quickly convert a column of text dates to real dates?
You can use the 'Text to Columns' feature. Select the column, navigate to the Data tab, click 'Text to Columns', choose 'Delimited', click Next twice, and in step 3 select 'Date' with the correct format (e.g., MDY). Finally, click Finish.
What causes a #VALUE! error in date subtraction?
A #VALUE! error occurs when you attempt to subtract a cell formatted as text from a cell formatted as a valid date. Excel cannot perform mathematical operations on text strings. You should use the ISNUMBER function to locate the text cell and convert it to a proper date serial.
