Calculation Issues

How to Fix Excel Displaying #### for Negative Dates and Times

Partner Editor Oct 9, 2026 868 views

Question details

The user needs a solution for Excel displaying hash symbols (####) when calculating date and time differences that result in negative values or when encountering invalid baseline entries.

How to Fix Excel Displaying #### for Negative Dates and Times

Product: Excel
Device & OS: not provided
Scenario: Calculating the difference between two dates or times where the resulting value is negative or one of the time entries is missing.
Observed behavior: Excel cells display an endless string of #### instead of rendering the negative date or time value, and changing the cell format does not resolve the issue.

Before you start

Verify that the #### error is not simply caused by a narrow column; double-click the column header boundary to auto-fit the width. If the hashes remain, it indicates a negative time calculation issue.

Solution 1Recommended

Use ABS and IF Formulas to Handle Negative Time Differences

Bypass Excel's limitation with negative times by calculating the absolute difference and using an IF condition to display whether the time is earlier or later.

By default, Excel uses the 1900 date system, which does not support negative times or dates. When you subtract a larger time from a smaller time, Excel produces a negative number and outputs ####.

To fix this without altering your entire workbook's date system, use the ABS function to calculate the absolute positive difference, and pair it with an IF function in an adjacent cell to specify the direction.

Calculate the absolute time difference

Select the cell where you want the time difference to appear and type the formula =ABS(C2-B2), assuming C2 and B2 contain your time values. Press Enter to get a positive time value.

Identify the time order

In the adjacent cell, type the formula =IF(C2>=B2,"Later","Earlier") and press Enter. This adds context to the absolute value, clearly showing the direction of the time difference.

Suppress invalid or missing results

If you want to leave the cell blank when encountering a missing entry or an invalid baseline (e.g., cell G10), use an IF formula to check for equality: =IF(ABS(G14-$G$10)=$G$10,"",ABS(G14-$G$10)).

Use ABS and IF Formulas to Handle Negative Time Differences

Formatting Limitations: Attempting to fix this specific issue by simply changing the cell format to General or Number will not produce correct time durations. You must use the formula approach to preserve the time format accurately.

Efficient Data Processing

Easily Calculate Complex Time Differences with WPS Spreadsheet

WPS Spreadsheet offers powerful, highly compatible formula tools that allow you to calculate complex time and date variables effortlessly. Use all your familiar formulas to manage data without running into unexpected calculation walls.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office, click on Spreadsheet, and open the document containing your time logs.
2. Apply the absolute value formula: Select your target calculation cell and enter =ABS(C2-B2) to immediately extract the correct time duration.
3. Use IF logic for context: In the adjacent cell, easily categorize the results by typing =IF(C2>=B2,"Later","Earlier").
4. Format and save: Format your cells as 'Time' using the right-click menu and save your perfectly compatible .xlsx file.

Advanced formula support including ABS, IF, and complex nested statementsFully compatible with Microsoft Excel (.xlsx) formats and date systemsFree to download with a lightweight installationFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show #### instead of a negative time?

Excel relies on the 1900 date system by default, which physically cannot process or display negative dates and times. Whenever a formula outputs a negative time value, Excel displays #### as a default error state to indicate it cannot render the value.

Can I switch to the 1904 date system to fix this issue?

Yes. You can switch to the 1904 date system by going to File > Options > Advanced, scrolling to 'When calculating this workbook', and checking 'Use 1904 date system'. However, be cautious: this will shift all existing dates in your current workbook by 4 years and 1 day, which can corrupt historical data.

Does making the column wider fix the #### error in Excel?

Widening the column only fixes the #### error if the underlying value is a positive number or a date that is simply too long to visually fit inside the current cell width. If the value is a negative time, widening the column will just display more hash symbols.

Excel & Data Problems