Fix Excel NOW Formula Showing Hash Signs in Time Difference
Question details
The user is trying to find out why calculating a time difference using the NOW function results in a string of pound or hash signs (####) and how to display the proper time value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the elapsed time difference between a past date/time and the current time using the NOW() function.
- Observed behavior
- The result cell displays pound signs (####) instead of the numerical time difference.
Ensure that your formula is subtracting the older past date from the current NOW() time. If you subtract a newer time from an older time, it results in a negative value which standard Excel time formats cannot display, also resulting in hash signs.
Increase the Column Width to Fit the Formatted Time Result
Hash signs (####) most commonly appear when a cell's column is too narrow to display the entire formatted number or time value.
When Excel calculates a time difference containing long decimal values or specific custom formats, the resulting string can exceed the default column width. Rather than truncating a number (which could be misleading), Excel fills the cell with pound signs as an alert.
Find the column containing your time difference calculation (for example, Column J) that is currently displaying ####.
Move your mouse cursor to the right edge of the column header letter at the top of the worksheet until the cursor turns into a double-sided arrow.
Double-click the column boundary to automatically expand the column width to perfectly fit the longest time value, or click and drag the boundary to the right to widen it manually.
Apply a Suitable Custom Time Format
If adjusting the column width does not remove the pound signs, the cell may be trying to display elapsed time using an incompatible format.
Calculate Time Differences Flawlessly in WPS Spreadsheet
WPS Spreadsheet provides excellent compatibility with advanced formulas, including NOW(), and features smart column auto-fitting to prevent display errors like hash signs when working with complex time formats.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your time calculations.
- 2. Enter the NOW formula: Select an empty cell and input your time difference formula, such as =NOW()-A2 (where A2 is the start time).
- 3. Apply time formatting: Right-click the cell, select Format Cells, and apply the custom format [h]:mm.
- 4. Auto-adjust width: Double-click the column header boundary to instantly snap the column width to the correct size, avoiding any #### errors.
Frequently Asked Questions
Why does my spreadsheet show #### even when the column is extremely wide?
If your column is sufficiently wide but still displays hash signs, it means your formula is resulting in a negative date or time. By default, spreadsheets use the 1900 date system, which cannot display negative times. Double-check your calculation to ensure you are subtracting the older time from the newer time.
How do I calculate the total hours between a past date and NOW?
Use a simple subtraction formula like =NOW()-A2, where A2 contains your past date stamp. To view the result as total hours, open the Format Cells dialog, select Custom, and enter [h]:mm in the Type box. The brackets prevent the hour count from resetting every 24 hours.
Can I force the spreadsheet to display negative times instead of hash signs?
Yes, but it requires changing the workbook's underlying date system. In Excel, go to File > Options > Advanced, scroll to the 'When calculating this workbook' section, and check 'Use 1904 date system'. Be aware that this will shift all existing dates in your workbook by four years, so it should be used with caution.
