Formatting Issues

How to Fix Hash Symbols When Subtracting Time Values in Excel

Maira Mehtab Oct 15, 2026 869 views

Question details

The user needs to fix an issue where calculating the difference between two time values results in a string of hash symbols instead of the actual time duration.

How to Fix Hash Symbols When Subtracting Time Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Subtracting time values in a spreadsheet, frequently using custom time formats like [h]:mm;@ to calculate total hours worked or elapsed time.
Observed behavior: The formula result cell displays hash symbols (#######) instead of the calculated time, even after attempting to adjust the column width using AutoFit.

Before you start

Before altering your formulas, ensure that both the start and end time cells are formatted correctly as Time and do not contain hidden text characters or accidental spaces.

Solution 1Recommended

Adjust Formulas to Handle Negative Time Differences

Use this method if your time calculation crosses midnight, which creates a negative time value that Excel cannot display under default settings.

Excel's default 1900 date system does not support the display of negative times or dates. When you subtract a later time on the previous day from an earlier time on the current day (e.g., a night shift crossing midnight), the result is mathematically negative, causing Excel to display hash symbols regardless of how wide the column is.

Select the formula cell

Click on the cell that is currently displaying the hash symbols to view its contents in the formula bar.

Apply the MOD function

Modify your existing subtraction formula (e.g., =B2-A2) by wrapping it in a MOD function. Change the formula to =MOD(B2-A2, 1).

Confirm and format

Press Enter. The cell will now display the correct positive time difference. Ensure the cell is formatted as Time (e.g., h:mm) to display the result properly.

Adjust Formulas to Handle Negative Time Differences

Alternative Formula: You can also use an IF statement to add a full day (24 hours) if the result is negative: =IF(B2<A2, B2+1, B2)-A2.

Solution 2

Widen the Column or Adjust Cell Formatting

Use this standard troubleshooting step to rule out the possibility that the calculated time string is simply too long to fit inside the cell boundaries.

Solution 3

Verify and Clean Underlying Time Values

Use this step if the first two methods fail, which often indicates that the underlying cell data contains microsecond anomalies or is stored as text.

Calculate Times Flawlessly

Manage Time and Formulas Easily in WPS Spreadsheet

WPS Spreadsheet provides robust formula capabilities and flexible formatting options, making it simple to calculate time differences, handle complex datasets, and track working hours without frustrating errors.

1. Open your workbook: Launch WPS Office and open your spreadsheet file in the WPS Spreadsheet application.
2. Enter your time data: Input your start and end times in the designated columns using standard time formats.
3. Calculate the duration: Type the formula =MOD(End_Time - Start_Time, 1) in the result cell to accurately calculate the difference, even if it crosses midnight.
4. Format the result: Right-click the cell, select Format Cells, and apply a Custom format like [h]:mm to display the total elapsed hours seamlessly.

Fully compatible with Microsoft Excel formulas, functions, and custom time formats.Lightweight and smooth performance even when handling complex time and date calculations.Intuitive cell formatting interface to quickly resolve display issues like hash symbols.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show ####### when calculating time?

Excel displays hash symbols (#######) either when a column is too narrow to display the entire cell content, or when a time or date calculation results in a negative value. Because Excel's default 1900 date system does not support negative time, it defaults to showing hash symbols.

Can I change Excel settings to display negative times?

Yes, you can enable the 1904 date system to display negative times. Go to File > Options > Advanced, scroll down to the 'When calculating this workbook' section, and check 'Use 1904 date system'. Be cautious, as this will shift all existing dates in your current workbook by four years.

What custom format should I use to display total hours over 24?

To display cumulative time that exceeds 24 hours, apply the custom number format [h]:mm. The square brackets around the 'h' instruct the spreadsheet program to calculate total hours rather than resetting to zero after every 24-hour cycle.

Document & File Issues