Subtracting times, negative result

[davej311]

Hi, I'm sure this is quite simple but I'm not getting it. I want to subtract to time values where the result (in this case) will be negative:
Time A is entered manually as e.g. 13:30
Time B is a result of a calculation and shows as 16:00
The result should be -2:30??
I've tried different ways but i can't get it to work.

 

[CsJHUN]

Hi davej311,

i guess you see ##### in cell, which is normal if the cell format is date/time. Change to numeric, or general, and you will see the numbers.
You could also use absolute formula, or if always time B is higher then simply change the substraction sides.
Yeah its pretty straightforward thinking, probably you already tried that. I could be need more info

Cheers
J

 

[joeu2004]

Generally, the Excel time formats will not display negative time. You should see that explanation if you hover the cursor over the "####" in the cell.

If you insist on wanting to see negative time, you have 3 options.


1. Change the Excel "date system" by setting the "Use 1904 date system" option. In Excel 2010, I click File > Options > Advanced and scroll down to find the option near the bottom.

I do not recommend this alternative because it changes the internal numerical representation of dates ("date serial number"). That can cause problems, especially when copy-and-pasting between workbooks.


2. Represent negative time as text. One way:

=IF(B2-A2 >= 0, B2-A2, TEXT(A2-B2, "\-[h]:mm"))

formatted as [h]:mm or however you want positive time to appear.

However, you cannot refer directly to cells with that formula in other arithmetic formulas. If that formula is in C2, you might do something like:

IF(ISNUMBER(C2), C2, --MID(C2,2,99))


3. In addition to #2 , you might calculate =B2-A2 formatted as General or other non-time numeric format in a helper cell, which you might hide.

Refer to the helper cell in other arithmetic formulas.