Calculation Issues

How to Fix Excel Date Subtraction Returning a Date Instead of Days

Steve K Oct 1, 2026 869 views

Question details

The user is attempting to calculate the difference between two dates using subtraction, but the result displays as a strange date (e.g., in the year 1900) or as ####### instead of the actual number of days.

How to Fix Excel Date Subtraction Returning a Date Instead of Days

Product: Spreadsheet
Device & OS: not provided
Scenario: Subtracting one date cell from another to determine the elapsed time or difference in days.
Observed behavior: The formula correctly calculates the difference in days, but the result cell automatically adopts a Date format, displaying the serial number as a date from 1900 or as an error string (#######) for negative values.

Before you start

Ensure that the cells containing your starting and ending dates are recognized as actual dates by your spreadsheet software, and not stored as plain text.

Solution 1Recommended

Change the Result Cell Format to General or Number

Reformat the calculation cell to display the underlying serial number difference as a standard numerical value.

Spreadsheet applications store dates as sequential serial numbers starting from January 1, 1900. When you subtract one date from another, the software calculates the correct number of days. However, it often automatically applies a 'Date' format to the result cell because the source cells are dates, converting that number of days into a date in early 1900.

Select the result cell

Click on the cell or cells containing your date subtraction formula (e.g., =D2-B2).

Access the Number Format menu

Navigate to the 'Home' tab on the top ribbon and locate the 'Number Format' dropdown menu in the formatting section.

Apply the new format

Click the dropdown and select 'General' or 'Number'. The cell will immediately update to show the integer representing the number of days.

Change the Result Cell Format to General or Number

Handling ####### Errors: If your subtraction results in a negative number and the cell is formatted as a date, it will display as ####### because standard date systems cannot process negative dates. Changing the format to General fixes this and correctly reveals the negative number.

Solution 2

Use the DAYS Function

Utilize a built-in spreadsheet function designed specifically to return the difference between two dates as a standard number.

Seamless Spreadsheet Management

Easily Calculate Dates and Data with WPS Spreadsheet

WPS Spreadsheet makes data calculation highly intuitive. By using WPS Office, you can efficiently calculate date differences, format cells in a single click, and utilize advanced functions with a smooth, user-friendly interface.

1. Open your file: Launch WPS Spreadsheet and open the document containing your dates.
2. Enter the formula: Select an empty cell and enter your subtraction formula, such as =A2-B2.
3. Open cell formatting: Right-click the result cell and select 'Format Cells' from the context menu.
4. Apply General format: Under the 'Number' tab, click on 'General' and press 'OK' to instantly view the correct number of days.

One-click cell formatting for dates, numbers, and custom values to prevent display errors.Fully compatible with Microsoft Excel (.xlsx) file formats and date systems.Built-in robust formula tools like DAYS and DATEDIF to avoid manual subtraction formatting issues.Lightweight, free alternative that runs smoothly on Windows, Mac, and Linux.

Frequently Asked Questions

Why does my formula show ##### when subtracting dates?

This usually happens when the subtraction results in a negative number and the cell is still formatted as a Date. Because the standard 1900 date system cannot display negative dates, it returns a string of hash symbols. Changing the cell format to 'General' or 'Number' will fix this and display the negative integer.

Can I calculate the exact number of months or years between dates instead of days?

Yes. Instead of simple subtraction, you can use the DATEDIF function. For example, =DATEDIF(start_date, end_date, "m") will return the number of complete months. Replacing "m" with "y" will return the number of full years.

How do spreadsheet programs store dates internally?

Spreadsheet software like Excel and WPS Spreadsheet stores dates as sequential serial numbers starting from January 1, 1900, which is serial number 1. January 2, 1900, is serial number 2, and so on. This system allows you to mathematically add and subtract dates just like regular numbers.

Excel & Data Problems