# Thread: Negative number of hours (Excel 2000)

1. ## Negative number of hours (Excel 2000)

Does anyone know how to make Excel show a negative amount of hours when subtracting a larger number from a smaller? For example, 40:00

2. ## Re: Negative number of hours (Excel 2000)

The time formats in Excel do not work with negative numbers. You can use a formula to display negative times.

Say that A1 contains the first time, and B1 the second time.
C1 contains the formula =A1-B1. C1 should be formatted to General, or some other number format. This cell can be used for further calculations.
In cell D1 (or any other cell), enter this formula:
=IF(C1<0,"-","")&TEXT(ABS(C1),"[hh]:mm")
This cell displays the negative time correctly. It is a text value, however, so you cannot use it directly in calculations.

3. ## Re: Negative number of hours (Excel 2000)

A yucky workaround that gets half way there is to use a custom format

[h]:mm:ss;-#,##0.00

This displays positive hours properly, and negative ones as a fraction of days. At least it can be used for maths in things such as totalling time.

An alternative is to keep the sign and number as separate cells

=abs(A1-B1) in one
and
=A1<B1 in the other

SUMPRODUCT or array formulae can then deal with the numbers for totals and the like.

4. ## Re: Negative number of hours (Excel 2000)

I'm not sure I like the custom format, but I use the method of keeping sign and absolute value in separate cells myself in some situations.

5. ## Re: Negative number of hours (Excel 2000)

Tools - option - calculation (tab) - check "1904 date system" and you can work with negative time

Steve

6. ## Re: Negative number of hours (Excel 2000)

Steve, I thought I'd read somewhere that this was a bug and contrary to design?
(But hey, if it works, it works.)

7. ## Re: Negative number of hours (Excel 2000)

I am not sure if it is "bug", it smacks of more "design".

I think it is allowed since excel works with dates from 1900 to 9999. If you pick the old 1904 date system, the first 4 years are negative (since day 1 is Jan 1, 1904), so it must be able to handle "negative date/time" with that system.

I am not sure "old dates" get "converted" so be aware that some sheets might be 4 years off if you entered dates in the other system and then changed the option for the workbook.

Steve

8. ## Re: Negative number of hours (Excel 2000)

Yes, existing dates are shifted by 4 years if you switch between the 1900 and 1904 date systems.

9. ## Re: Negative number of hours (Excel 2000)

Thanks very much to everyone for all these responses. I'm trying out the option that Steve suggested first, simply because it looks the easiest. I was relieved to see that the option change only affects the current file. The fact that existing dates are increased by four years is not a problem - I have adjusted that in my test file. I am making a timesheet which is to be used by everyone in my company, and as some of them are very non-computer-literate I am trying to keep things as transparent as possible.

I will try out the other methods as well, just in case the option change causes problems and I have to reverse it later on.

Thanks again.

June

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•