Excel Error Codes

How to Fix Excel #VALUE! Error from Negative Date Differences

Adam Davis Oct 23, 2026 869 views

Question details

The user encounters a #VALUE! error in Excel when calculating the difference between two date and time values, resulting in a negative duration.

How to Fix Excel #VALUE! Errors from Negative Date Differences

Product: Excel
Device & OS: not provided
Scenario: Calculating the duration or difference between two date/time cells where the start date is later than the end date.
Observed behavior: Excel displays a #VALUE! error or a string of hash marks (###) because the default 1900 date system cannot process or display negative time values.

Before you start

Check your date and time cells to ensure they are formatted correctly as Dates or Times, and verify which cell represents the start time versus the end time.

Solution 1Recommended

Use an IF Function to Handle Negative Time Differences

Prevent the #VALUE! error by using an IF formula that checks which date is larger before calculating the difference.

Because Excel's default 1900 date system cannot display negative times, subtracting a later date from an earlier one results in an error. An IF function can output a custom text message or formatted string instead of failing.

Select the result cell

Click on the cell where you want the calculated duration to appear.

Enter the IF formula

Type the formula =IF(C3>=H3,INT(C3-H3)&" days "&TEXT(C3-H3,"h hrs m mins"),"Negative duration"), replacing C3 with your later date and H3 with your earlier date.

Apply the calculation

Press Enter. If the result is negative, the cell will now display the text 'Negative duration' instead of a #VALUE! error.

Use an IF Function to Handle Negative Time Differences

Customizable output: You can change 'Negative duration' in the formula to any text you prefer, such as 'Check dates' or '0'.

Solution 2

Subtract the Earlier Date from the Later Date

Ensure your formula always subtracts the smaller (earlier) date and time from the larger (later) one to yield a positive duration.

Resolve Date Errors Easily

Calculate Date and Time Differences Seamlessly in WPS Office

WPS Spreadsheet offers robust cell formatting options and complete compatibility with complex formulas, making it easy to calculate date and time durations without frustration.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your date and time data.
2. Enter the error-handling formula: Select the cell for your duration output and enter the IF formula to handle potential negative times effortlessly.
3. Customize cell formatting: Right-click the cell, choose 'Format Cells', and select custom time categories to perfectly display your date differences.

Fully compatible with Microsoft Excel date and time formulas (IF, TEXT, INT)Intuitive custom cell formatting for precise duration displayFree and lightweight alternative for powerful spreadsheet managementBuilt-in error checking to help identify and resolve #VALUE! triggers

microsoft office alternative - wps office

Frequently Asked Questions

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

By default, Excel uses the 1900 date system, which does not support negative dates or times. When a calculation results in a negative time, it displays a series of hash marks (###) or a #VALUE! error to indicate it cannot render the value.

Can I change Excel's date system to show negative times?

Yes, you can switch to the 1904 date system by going to File > Options > Advanced, and checking 'Use 1904 date system'. This allows negative times to display correctly, but be aware that it will shift all existing dates in your workbook by four years.

How do I calculate just the absolute difference between two times?

You can use the ABS function, such as =ABS(End_Time - Start_Time). This will always return a positive value, bypassing the negative time error completely, though it will not indicate which time was earlier.

Microsoft Office Error Codes