Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 74 Display Negative Time Values

Sometimes you want to display negative time
values, but Excel will only display ######. There
are several ways to escape this problem.

If you enter the time
12::00::00 (midday) into any cell and then
format it as General, you will see that it has a numeric value of
0.5. Similarly, enter the time 24::00::00 in
any cell. Look in the Formula bar and you will see that Excel shows
1/1/1900 12::00::00 AM.

Excel sees dates and times as nothing more
than numbers. In the case of a date, by default Excel considers 1
January 1900 to have a numeric value of 1; 2 January 1900 a numeric
value of 2; and so forth. Times are seen as decimals, with the
exception of midnight, which has a numeric value of 1. (See [Hack #38] for full details.) Because of
this, Excel has real trouble coping with the notion of negative time.

Here are three methods to get Excel to display negative time values.

Method 1: Changing Excel's Default Date System

One
quick and easy way to display negative values is to simply select
Tools Options Calculation and check the 1904
date system checkbox. (The 1904 date system is also called Macintosh
dates and times.)

Be aware that doing this will cause Excel to change the starting dates from which all cells are calculated from 1 January 1900 to 2 January 1904. Changing this option will affect only the workbook in which you make the change.

Now you will be able to subtract times from each other and have the
result displayed as a negative time value.

Try it. Select Tools
Options Calculation and check the 1904 date system
checkbox. Enter 5::00::00 AM in cell A2. In
cell A3, enter 6::00::00 AM. In cell A4, type
=A2-A3.

You will get the result of -1:00, but only if you
checked the 1904 date system checkbox as described earlier.

If
changing Excel's default date system to the 1904
date system is likely to cause problems within the workbook for other
time calculations, you need to use another method that will change
the appearance of a cell housing a negative value.

Method 2: Using the TEXT Function

The second method requires the use of the TEXT
function. To begin, enter 5::00::00 AM in cell
A2. In cell A3, enter 6::00::00 AM. In cell
A4, type the following:

=TEXT(MAX($A$2:$A$3)-MIN($A$2:$A$3),"-H::MM")

With this nested formula, you are
subtracting A3 from A2 to give a positive time value. Then you are
formatting the cell using the TEXT function so
that it shows a negative time. Using the combination of the
MAX and MIN functions ensures
that you are always subtracting the earlier time from the later time.

You need to be aware that the result returned is actually a text value, not a numeric value, in case you intend to use the result in another formula.

Method 3: Using a Custom Format

One final way you can display negative
times, without changing to the 1904 date system and still returning a
true numeric value, is to select Format Cells
Custom and use a Custom format of -h:mm.

This method works only if you always want a
negative time value displayed. It also requires that you always
subtract the earlier time from the later time. This means
all times returned really will be positive and
will only appear negative.

So, by using any one of these three methods, you will be able to
display negative times. Just remember that there are pitfalls to each
of them, so always use them with these pitfalls in mind.