Excel Guides

Displaying Negative Times in Excel

When working with time values in Excel, it's important to understand how negative times are displayed. By default, negative time values are displayed as ####. This can be frustrating when you're trying to track down the source of an error in a worksheet. In this article, we'll take a look at how to display negative times in Excel so that you can more easily spot errors.

The first thing to understand is that Excel stores time values as fractions of a day. So, a time value of 1 hour would be stored as 1/24, 2 hours would be stored as 2/24, and so on. This is why negative time values are displayed as #### - the fraction is less than 0, so it can't be displayed properly.

There are a few ways to work around this issue. One is to use the TEXT function to format the cells containing negative time values. For example, if you have a cell with a value of -1 hour, you could use the following formula:

=TEXT(A1,"-hh:mm")

This would display the value as -01:00. You can also use other formats in the TEXT function, such as "hh:mm:ss" or "d-mmm-yyyy hh:mm".

Another way to work around this issue is to use conditional formatting. For example, you could use a conditional format that turns the text color red for any cells with a value less than 0. To do this, select the cells you want to format and then click on Conditional Formatting > Highlight Cell Rules > Less Than from the ribbon. Then enter 0 in the value field and choose your desired formatting options.

If you want to hide negative time values completely, you can use a custom number format. For example, you could use the following number format:

[h]:mm:ss;@

This number format will display positive time values as usual, but it will hide negative time values (and any other non-time values).

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.