Excel, calculate hh:mm time difference
I have 2 cells (A1, A2), formatted as [h]:mm for example:
Example 1
| A | |
|---|---|
| 1 | 40:00 |
| 2 | 30:00 |
| 3 | Here should display -10:00
|
In cell A3, I want the hours:minutes difference between the 2 cells, in the same format ([h]:mm). In the above example, in A3 it should display -10:00.
Example 2
| A | |
|---|---|
| 1 | 40:00 |
| 2 | 45:00 |
| 3 | Here should display 5:00
|
I have tried =TEXT(A1-A2, "[h]:mm") in A3 which works when A2 is smaller than A1, but in example 1 it will display 10:00 instead of -10:00 and in example 2, it will error.
How could I achieve that calculation?
Solution 1:
Add the - with the text format to have a "negative" time.
=TEXT(A1-A2,"-[h]:mm")
For both cases, use:
=IF(A1>A2,TEXT(A1-A2,"-[h]:mm"),A2-A1)