Fix Excel Date Formula Returning #VALUE! When Adding One Day
Question details
The user is encountering a #VALUE! error when attempting to add one day to an existing date using a simple addition formula.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Trying to increment a date by one day using a mathematical formula, such as adding +1 to a cell reference.
- Observed behavior
- The formula returns a #VALUE! error instead of the next sequential date, which typically occurs when the source cell is stored as text rather than a valid date.
Verify your computer's regional date settings, as spreadsheet software relies on these system settings to correctly interpret and recognize typed dates.
Convert Text to Date Format and Re-enter Data
Change the cell format from Text to a valid Date format so the mathematical formula can calculate properly.
Formulas like =B7+1 require the source cell to be a true numeric date. If the original date was typed while the cell was formatted as Text, the spreadsheet cannot perform math on it, resulting in a #VALUE! error.
Click on the cell containing the original date that you are referencing in your formula (e.g., cell B7).
Right-click the selected cell, choose 'Format Cells', and select 'Date' or 'General' from the Number tab instead of 'Text'.
Delete the existing text value in the cell and type the date again using your system's correct regional date format (such as MM/DD/YYYY).
Enter the formula =B7+1 in your target cell and press Enter. It should now display the next consecutive date.

Bulk Convert Text Dates Using Text to Columns
Use the Text to Columns feature to quickly convert an entire column of text-formatted dates into true Excel dates without retyping.
Use the DATEVALUE Function to Force Conversion
If you do not want to alter the original data, you can use the DATEVALUE function in a helper column to convert text into readable serial dates.
Easily Manage Dates and Formulas with WPS Spreadsheet
WPS Spreadsheet offers intuitive formatting tools and error-checking features to effortlessly handle date calculations and avoid #VALUE! errors when working with large datasets.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the #VALUE! errors.
- 2. Select the problematic dates: Highlight the cells that contain the dates stored as text.
- 3. Reformat instantly: Right-click and select 'Format Cells' to seamlessly switch the data to a standard Date format.
- 4. Calculate with ease: Type your formula =Cell+1 and drag the fill handle down to effortlessly auto-populate the rest of your column.

Frequently Asked Questions
Why does my date change to a random 5-digit number when I format it?
Spreadsheet software stores dates as sequential serial numbers starting from January 1, 1900. A 5-digit number like 44204 is simply the underlying serial number. Just apply a Date format from the 'Format Cells' menu to display it correctly as a calendar date.
How can I easily tell if my date is formatted as text?
By default, true numerical dates are aligned to the right side of a cell, whereas dates formatted as text are aligned to the left. You can also verify this by using the formula =ISTEXT(Cell), which returns TRUE if the date is stored as text.
Can I add a full month to a date instead of just days?
Yes. Instead of adding a fixed number of days with a + sign, use the EDATE function (e.g., =EDATE(B7, 1)) to add exactly one month to a date, which automatically accounts for varying days in different months.
Why does the DATEVALUE function also return a #VALUE! error?
DATEVALUE only works if the text string resembles a date format that is recognized by your computer's regional settings. If the text format is entirely unrecognizable, such as having misspelled month names or unsupported delimiters, the function will fail.




