Calculation Issues

How to Fix Excel Not Displaying Negative Time Correctly

Maira Mehtab Sep 22, 2026 869 views

Question details

The user is unable to correctly display negative time values (formatted as hh:mm:ss) after pasting them from a Microsoft Teams chat into a spreadsheet.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Pasting negative time values directly from a Microsoft Teams chat into an Excel workbook for tracking or calculation.
Observed behavior: The pasted negative time values appear left-aligned, are treated as text instead of valid time values, lack seconds, and do not respond to custom cell number formatting.

Before you start

Before modifying your workbook's settings, check whether the time values are direct text entries or formula results, as applying standard number formats to text strings will not convert them into calculable time data.

Solution 1Recommended

Enable the 1904 Date System

Excel's default 1900 date system does not support the display of negative times or dates. Switching to the 1904 date system allows the application to calculate and display negative time values appropriately.

By default, Excel uses the 1900 date system, which turns any negative time calculations into a string of pound signs (#####) or treats them as unrecognized text. Changing the workbook calculation settings to the 1904 date system resolves this limitation.

Access Excel Options

Click on 'File' in the top left corner of the Excel ribbon, then select 'Options' at the bottom of the left sidebar.

Navigate to Advanced Settings

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

Enable the 1904 Date System

Scroll down to the 'When calculating this workbook' section. Check the box next to 'Use 1904 date system'.

Apply and Confirm

Click 'OK' to save the settings. Your negative time formulas should now display correctly instead of showing errors.

Warning regarding existing dates: Enabling the 1904 date system will shift all existing dates in your current workbook by four years and one day. It is highly recommended to use this setting in a new workbook or ensure no standard dates are present before switching.

Solution 2

Clean and Convert Text-Based Time Values

Values copied from applications like Microsoft Teams are often pasted as text strings (indicated by being left-aligned). You must convert them to standard numeric values before formatting them as time.

Seamless Spreadsheet Alternative

Easily Calculate and Display Negative Times in WPS Spreadsheet

WPS Office offers a powerful, lightweight, and highly compatible spreadsheet application that seamlessly supports advanced calculations, including negative time values. It provides the same flexible date system settings as Microsoft Excel while offering built-in data cleaning tools.

1. Open WPS Spreadsheet Options: Launch WPS Spreadsheet, click the 'Menu' button in the top left corner, and select 'Options'.
2. Go to Calculation Settings: In the Options dialog, navigate to the 'Calculation' tab in the left-hand menu.
3. Enable 1904 Date System: Under the Workbook options, check the box for '1904 date system' and click 'OK'. You can now calculate negative time values seamlessly.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and formatsNative support for enabling the 1904 date system to calculate negative timeIntuitive UI that requires no steep learning curve for Office usersFree and lightweight alternative for seamless data manipulation

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show ##### instead of a negative time?

By default, Excel uses the 1900 date system, which does not support negative times or dates. When a calculation results in a negative time value, Excel displays a row of pound signs (#####) to indicate the error.

Why are my pasted time values left-aligned in the cell?

If your time values are left-aligned, Excel is treating them as text rather than numerical data. This often happens when pasting data from chat apps like Teams or web browsers. Changing the number format won't work until you convert the text to a number using tools like 'Text to Columns'.

Will changing to the 1904 date system mess up my other dates?

Yes. Switching to the 1904 date system shifts all previously entered dates in that specific workbook by four years and one day. It is best to use this setting only in workbooks dedicated exclusively to time duration calculations, or configure it before entering standard dates.

Excel & Data Problems