How to Fix the Excel DATEVALUE and TODAY #VALUE! Formula Error
Question details
The user needs to resolve a #VALUE! error occurring when attempting to subtract today's date from a specific cell using a formula combining SUM, DATEVALUE, and TODAY.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the difference in days between a date value in a specific cell and the current date.
- Observed behavior
- The formula =SUM(DATEVALUE(G2))-TODAY() returns a #VALUE! error.
Verify the exact format of your target cell (e.g., G2). The correct formula depends entirely on whether the cell contains a date formatted as a text string or a true Excel date serial number.
Correct the Formula for Text Dates
Remove the unnecessary SUM function and use DATEVALUE correctly if the target cell contains a date stored as text.
The SUM function is redundant when performing simple subtraction. If your date is stored as text, you only need to convert it using DATEVALUE and then subtract TODAY().
Click on the cell displaying the #VALUE! error to make it active.
Click into the Formula Bar at the top of the window and delete the existing =SUM(DATEVALUE(G2))-TODAY() formula.
Type =DATEVALUE(G2)-TODAY() and press the Enter key to calculate the result.
Simplify Formula for Real Excel Dates
Use a direct subtraction formula if the target cell already contains a valid Excel date instead of text.
Clean Invalid Data in the Reference Cell
Fix invalid text, hidden spaces, or unsupported regional date formats in the reference cell causing Excel to reject the formula.
Easily Manage Date Formulas with WPS Spreadsheet
WPS Spreadsheet provides powerful date calculation tools, intelligent formula prompting, and excellent error tracing. Easily find and fix formula issues like the #VALUE! error without the hassle.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date calculations.
- 2. Use formula prompts: Select a blank cell, type '=', and start typing 'DATEVALUE'; WPS will display a helpful syntax guide.
- 3. Calculate instantly: Enter =G2-TODAY() or =DATEVALUE(G2)-TODAY() depending on your cell format, and press Enter to get the result.
Frequently Asked Questions
Why does the DATEVALUE function return a #VALUE! error?
This error typically occurs if the referenced text is not in a recognized date format that matches your system settings, or if the referenced cell already contains a numeric date serial instead of a text string.
Do I need to use the SUM function when calculating the difference between two dates?
No, the SUM function is completely unnecessary for simple subtraction. You can directly subtract one date from another using the minus sign, such as =A1-B1.
How do I calculate the number of days until a future date?
Assuming the future date is a valid date located in cell A2, you can use the formula =A2-TODAY(). Ensure the cell where you enter this formula is formatted as a General number, not as a Date.
