How to display / show negative time properly in Excel?
Working with time calculations in Excel can be challenging when subtracting a later time from an earlier time. For example, if you subtract12:20 from10:15, Excel returns a ###### error rather than a negative time value, as illustrated in the screenshots below. This problem commonly occurs because Excel, by default, only supports positive time values in its standard date system. Resolving this issue is important for accurate scheduling, timesheet management, and any situation where you need to reflect negative time durations or overtime calculations.
➤ Display negative time properly by changing Excel’s default date system
➤ Display negative time properly with Formulas
➤ Display negative time using VBA code (custom text format)
Display negative time properly by changing Excel’s default date system
When working with time calculations, one straightforward way to display negative time values is to switch the workbook to the 1904 date system. This lets negative results show correctly (e.g., “-2:05”) instead of errors. It’s handy when you frequently need to show negative durations, such as in project tracking or shift planning.
Notes:
- Switching to the 1904 date system shifts all existing dates in the workbook by about 1,462 days (~4 years). Review other date fields after the change.
- This setting is workbook-specific (it does not affect other files).
- For collaboration, be aware that users on the default 1900 date system may see different results if workbooks are mixed.
- You cannot use both the 1900 and 1904 date systems in the same workbook at the same time.
1. Open the Excel Options dialog. In Excel 2010/2013, go to File > Options. In Excel 2007, click the Office Button and choose Excel Options.
2. In Excel Options, click Advanced. Under When calculating this workbook, check Use 1904 date system. See the screenshot below:
3. Click OK. Negative time differences will now display correctly, as shown here:
Tips:
- If preserving historical date accuracy is critical, consider alternative approaches (e.g., custom text output via formula or VBA) instead of switching date systems.
- Document the date-system change in the workbook (e.g., on a “ReadMe” sheet) to prevent confusion for other users.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Display negative time properly with Formulas
If your workflow does not allow changing the date system (for compatibility or reporting reasons), you can achieve correct negative time display using formulas. This method displays negative durations as text, allowing for clear visual results without triggering Excel's error. This approach is widely used in scenarios such as work hour deficit calculations, project overrun tracking, or elapsed time comparisons.
1. Enter the times you want to calculate in two separate cells (e.g., A1 and A2). In a blank cell, type the following formula to calculate and format the negative difference:
=TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-H::MM")Here, A1 and A2 represent the two time values. Adjust cell references as needed for your data. This formula delivers a result as text, so you can see negative signs if required, even if Excel would normally return an error in calculation.
2. Press Enter. The result will appear as a formatted time string with a minus sign if it is negative, as shown below:
Alternative Formula:
You may also use this formula, which conditionally displays a minus sign when the result is negative:
=IF(A2-A1<0, "-" & TEXT(ABS(A2-A1),"hh:mm"), A2-A1)In this setup, A2 is the smaller or earlier time, and A1 is the later time, but you should adjust these as appropriate for your calculation direction. This method produces a text result for negative values, making it easier for reporting, but also note that resulting values cannot be totaled or summed as times without additional conversion.
Notes:
- Results are text, not true time values, so further time calculations will not work unless you convert the output back to a time format.
- Formula approach is workbook-independent, useful for sharing, and avoids global setting conflicts.
- Be mindful of referencing correct cell directions to avoid logical errors (e.g., subtracting end from start to get a negative).
Display negative time using VBA code (custom text format)
Another effective method to handle negative time in Excel is by using a VBA macro. This is particularly useful when you want a seamless way to automatically detect and display negative time calculations in your worksheet—even for large batches—without changing system settings or relying on formulas for every cell. This method is suitable for advanced users managing ongoing time data entry, such as employee shift logs, and prevents the ###### error by converting negative time results to custom text strings (e.g., prefixed with a minus sign).
Pros: Allows for batch processing of selected cells, preserves original data, can format results as needed, and does not affect Excel’s date system settings.
Cons: Requires enabling macros, not suitable if you want to maintain Excel's native time values for calculation beyond display.
Care and tips:
- Always save a backup of your file before running VBA scripts.
- This solution transforms negative time results into text. If future calculations are needed, use the original numerical data, not the macro-generated display result.
- Macros may be disabled by your IT policies. Enable macros in Excel to use this solution.
1. In Excel, click Developer > Visual Basic. In the VBA editor window, click Insert > Module, then paste the following code into the module window:
Sub DisplayNegativeTimeAsText()
Dim WorkRng As Range
Dim Cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select time calculation result cells", xTitleId, WorkRng.Address, Type:=8)
For Each Cell In WorkRng
If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
If InStr(Cell.NumberFormat, ":") > 0 Then
If Cell.Value < 0 Then
Cell.NumberFormat = "@"
Cell.Value = "-" & Format(Abs(Cell.Value), "h:mm")
End If
End If
End If
Next
End Sub2. To execute the macro, click the 
Run button in the VBA editor or press F5. A dialog box will appear allowing you to select the range of cells containing your time difference results (even those showing the ###### error).
The macro will automatically scan the selected range. For any cell containing a negative time value (which would normally show ######), it changes the cell to text format and converts the value to a readable format, such as "-2:05". Positive time results remain unchanged. If you want to revert to the normal time format, you must restore original cell values or use an undo/restore action.
Troubleshooting and further tips:
- If you select a range without time differences (e.g., blank cells or non-time values), the macro skips them safely.
- If no cells are selected, the macro will prompt for a range and not affect other data.
- Always review the formatting after execution; the affected cells are now text, so original time calculations won’t work until reset.
- To expand or adapt the macro for other display formats or longer hour-minute-second structures, modify the
Format(Abs(Cell.Value), "h:mm")section as needed.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in