Calculation Issues

How to Fix Red Hash Marks for Negative Times in Excel

Tauseeq Magsi Sep 28, 2026 869 views

Question details

Users need to correctly calculate and display negative time values without triggering the red hash marks error.

How to Fix Red Hash Marks for Negative Times in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Subtracting a larger time value from a smaller time value to calculate a time difference or track owed hours.
Observed behavior: Cells display a continuous string of red hash marks (########) instead of a negative time value because the default 1900 date system does not support negative dates or times.

Before you start

Before changing any workbook date settings, check if your file already contains standard dates, as modifying the date system will shift all existing dates by four years.

Solution 1Recommended

Enable the 1904 Date System

Switch the workbook to use the 1904 date system, which natively supports and displays negative time values.

By default, Excel uses the 1900 date system, which cannot evaluate negative numbers as dates or times. Switching to the Mac-originated 1904 date system resolves this limitation instantly, though it should be used cautiously on workbooks with existing date entries.

Open Excel Options

Click on the 'File' tab in the top-left corner of the ribbon, then select 'Options' at the bottom of the menu.

Navigate to Advanced Settings

In the Excel Options dialog box, click on the 'Advanced' tab in the left-hand pane.

Locate Calculation Settings

Scroll down the Advanced settings page until you find the section labeled 'When calculating this workbook'.

Apply the 1904 Date System

Check the box next to 'Use 1904 date system' and click 'OK'. Your negative times will now display correctly with a minus sign instead of hash marks.

Warning for Existing Dates: Changing to the 1904 date system will shift all previously entered dates in your workbook forward by four years and one day.

Solution 2

Format Negative Times as Text Using Formulas

Use the TEXT and ABS functions to bypass the 1900 date system limitation, preserving your existing dates.

Seamless Time Calculations

Calculate and Display Negative Times with WPS Spreadsheet

WPS Spreadsheet provides robust formula support and flexible workbook calculation settings, allowing you to seamlessly handle negative times using either the 1904 date system or advanced TEXT functions.

1. Open Your Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
2. Access Options: Click the 'Menu' button in the top-left corner and select 'Options' from the drop-down list.
3. Go to Calculation Settings: In the Options dialog, click on the 'Calculation' tab located in the left sidebar.
4. Enable 1904 Date System: Check the box for '1904 date system' under the workbook settings and click 'OK' to instantly fix the hash marks.

100% compatible with Microsoft Excel formulas and date systemsEasily toggle the 1904 date system without complicated menusLightweight architecture ensures fast calculations on large datasetsFree to use for everyday data management and spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my spreadsheet show ###### for negative times?

By default, spreadsheets use the 1900 date system, which does not recognize negative values for dates or times. When a calculation yields a negative time, the software displays red hash marks (########) to indicate the value cannot be displayed under the current formatting.

Will switching to the 1904 date system ruin my other dates?

Yes, if your workbook already contains dates entered under the default 1900 system. The 1904 system starts its timeline on January 1, 1904. Enabling it will shift all existing dates forward by four years and one day. It is best used for new workbooks or files containing only time calculations.

Can I still use the text-formatted negative times in further calculations?

No. When you use the TEXT and ABS functions to add a minus sign (e.g., ="-"&TEXT(ABS(A1-B1),"hh:mm")), the output becomes a text string. Standard time and date formulas cannot perform math on text values.

Excel & Data Problems