MS-Excel / Functions and Formula

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.

[Previous] [Contents] [Next]

In this tutorial:

  1. Display Negative Time Values
  2. Method 1: Changing Excel's Default Date System
  3. Method 2: Using the TEXT Function
  4. Method 3: Using a Custom Format