How to Fix Excel Negative Time Results Showing as Hash Marks
Question details
The user needs to display a correct time from the previous day without Excel showing hash marks (######) when a time calculation or time-zone conversion results in a negative value.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Subtracting time or converting time zones where the resulting time drops below zero and falls into the previous day.
- Observed behavior
- The spreadsheet displays a string of hash marks (######) instead of the calculated time because the resulting time value is negative.
Ensure that the cell containing your calculated result is formatted specifically as 'Time'. Spreadsheets using the default 1900 date system cannot process negative times, which triggers the hash mark error.
Use the MOD Function to Handle Negative Time
Applying the MOD function ensures the calculated time remains within a valid 24-hour period, preventing the formula from returning negative values.
Spreadsheets store dates and times as serial numbers where the value 1 equals 24 hours. When subtracting time (such as a time-zone offset), the result might drop below zero, causing the application to display hashes (######). Using the MOD function with a divisor of 1 forces the negative decimal back into a valid positive time component representing the correct time on the previous day.
Click on the cell where you want the calculated time to appear.
Type the formula using the MOD function. For example, to subtract 1 hour (represented as 1/24) from cell D10, type =MOD(1+D10-(1/24), 1).
Press Enter to apply the formula and view the numeric result.
Right-click the cell, choose 'Format Cells', navigate to the 'Number' tab, and apply a 'Time' format to display the result correctly.
Add a Full Day (1) to the Subtraction Formula
A simple manual workaround is to add 1 (representing a full 24-hour day) directly to your subtraction formula to keep the time positive.
Perform Advanced Time Calculations in WPS Spreadsheet
WPS Spreadsheet fully supports complex time calculations, including the MOD function, allowing you to seamlessly manage time-zone conversions, payroll, and shift schedules without encountering negative time formatting errors.
- 1. Open your time data: Launch WPS Spreadsheet and open the document containing the time values you need to convert or subtract.
- 2. Apply the time conversion formula: In your target cell, input the time conversion formula such as =MOD(1+A1-(1/24),1) to offset negative hours safely.
- 3. Format cell as Time: Right-click the selected cell, click 'Format Cells', go to the 'Number' tab, and choose 'Time' to display the results accurately.
Frequently Asked Questions
Why does my spreadsheet show ###### for negative times?
By default, spreadsheets use the 1900 date system, which does not support negative dates or times. When a calculation (like subtracting an hour from 12:30 AM) results in a negative value, the application cannot render it as a standard time and displays hash marks (######) instead.
Can I change a setting to display negative times without using formulas?
Yes, you can switch to the 1904 date system to allow negative times. Go to File > Options > Advanced, scroll down to the section 'When calculating this workbook', and check 'Use 1904 date system'. Be aware that this will shift all existing dates in your workbook by four years, so it is usually safer to use the MOD formula.
How do I safely subtract hours from a time value?
To subtract a specific number of hours, you can divide the hours by 24 to convert them into a standard serial number. For example, to subtract 3 hours from cell A1, use the formula =A1 - (3/24). If the result might cross midnight into the previous day, wrap it in the MOD function: =MOD(1+A1-(3/24), 1).
