Convert negative time to negative decimal

You need to use the 1904 date system to display negative times:

	A	B	C	D
1	Time
2	-135:40	Sum Pos	26.47	C2: =SUMIF($A$2:$A$22, ">0")
3	131:05	Sum Neg	-38.29	C3: =SUMIF($A$2:$A$22, "<0")
4	106:16
5	9:32
6	-140:59
7	-134:01
8	-53:15
9	-46:10
10	-8:45
11	-107:40
12	-63:45
13	43:31
14	55:07
15	-1:12
16	-48:39
17	-141:27
18	-37:23
19	91:40
20	78:19
21	61:56
22	57:43

Since Excel does not normally support negative time [1], I presume that what appears to be "-7:30" is actually text, not numeric. That would explain why "-7:30"*24 results in a #VALUE error.

The following should work:

=TEXT(SUMPRODUCT( (LEFT(A1:A4,1)="-")*(--MID(A1:A4,2,99)) ), "\-[h]:mm")

Caveat: I assume that 7:30 is 7 hours 30 minutes, not 7 minutes 30 seconds.

PS.... To display as a decimal number:

=-SUMPRODUCT( (LEFT(A1:A4,1)="-")*(--MID(A1:A4,2,99)) ) * 24


-----
[1] Unless you select the 1904 date system, which I do not recommend unless you have a Mac file.

Hi, thanks for the replies.

1, Negative times display correctly on the spreadsheet (-7:30, yes it is 7 hours 30 mins).
2, I have been using 1904 date with no success.

In the attached, column A is a sample of the data received (could be well in excess of 1000 rows)
Column B is the result of running SIGN() on the value
Column C is the result of trying to convert to a decimal
Column D is my manual entry of what i need the data to be (D4 should be 7.25 not 7.50 :-))
Column E is the new result of running SIGN() on what the data should be
And the results are just that, the results/

Your workbook wasn't set to use the 1904 date system, and all your times are text. With default horizontal alignment, numbers always align right (unless formatted as Text). Negative time values need to be entered as, for example, -"7:15"

	A	B	C	D
1	Time Balances		Result
2	0:00
3	60:00	Total Minus	-29.10	C3: =SUMIF(A2:A16, "<0") * 24
4	-7:15	Total Plus	420.60	C4: =SUMIF(A3:A17, ">0") * 24
5	142:30
6	11:45
7	0:00
8	0:00
9	93:00
10	30:51
11	0:00
12	82:30
13	-21:51
14	0:00
15	0:00
16	0:00

Re: test sheet.xls

Enter the following into C2 and copy down:

=IF(ISNUMBER(--A2), A2, -MID(A2,2,99))*24

Enter the following SUMIF formulas:

H3: =SUMIF(C2:C16,"<0")
H4: =SUMIF(C2:C16,">0")

Or if you want to avoid the "helper" cells in column C, array-enter the following (press ctrl+shift+Enter instead of just Enter):

H3: =SUM(IF(ISNUMBER(--A2:A16)=FALSE, -MID(A2:A16,2,99)))*24
H4: =SUM(IF(ISNUMBER(--A2:A16), A2:A16*24))

By the way, your expectations in column D are wrong in some cases. -7:15 is -7.25, not -7.50. -21:51 is -21.85, not -21.80. Of course, that causes differences in H3, as well.


-----
FYI, one reason to avoid setting the 1904 Date System option: you will encounter problems when copying bona fide dates (not the text that you have) between workbooks that do not have the 1904 Date System set (most common).


-----
[EDIT] You could also array-enter the following equivalent formulas into H3, whatever you prefer:

=SUM(IF(ISNUMBER(--A2:A16), FALSE, -MID(A2:A16,2,99)))*24
or
=SUM(IF(NOT(ISNUMBER(--A2:A16)), -MID(A2:A16,2,99)))*24