Formula Errors

Fix Excel Date Formula Returning #VALUE! When Adding One Day

Phi Hung VoPhi Hung Vo Nov 3, 2026 868 views

Question details

The user is encountering a #VALUE! error when attempting to add one day to an existing date using a simple addition formula.

How to Fix Excel Date Formula Returning #VALUE! When Adding a Day
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.
Before you start

Verify your computer's regional date settings, as spreadsheet software relies on these system settings to correctly interpret and recognize typed dates.

Solution 1Recommended

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.

1
Select the source cell

Click on the cell containing the original date that you are referencing in your formula (e.g., cell B7).

2
Change the cell format

Right-click the selected cell, choose 'Format Cells', and select 'Date' or 'General' from the Number tab instead of 'Text'.

3
Re-enter the date

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).

4
Apply your formula

Enter the formula =B7+1 in your target cell and press Enter. It should now display the next consecutive date.

Convert Text to Date Format and Re-enter Data
Alignment Tip: By default, true dates are right-aligned in a cell, while text strings are left-aligned. Use this visual cue to quickly spot text-formatted 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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the #VALUE! errors.
  2. 2. Select the problematic dates: Highlight the cells that contain the dates stored as text.
  3. 3. Reformat instantly: Right-click and select 'Format Cells' to seamlessly switch the data to a standard Date format.
  4. 4. Calculate with ease: Type your formula =Cell+1 and drag the fill handle down to effortlessly auto-populate the rest of your column.
Intelligent text-to-date conversion to quickly prevent formula errorsFully compatible with Microsoft Excel formulas and date formatsFree and lightweight spreadsheet tool for everyday data processing
microsoft office alternative - wps office

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.