I am trying to sum two cells that have hh:mm's in them. Some of these cells may start with a + that I have removed but other are negative.
The highlighted cell has this formula in it.
=SUM(IF(LEFT(R6148,1)="+",RIGHT(R6148,LEN(R6148)-1),R6148),IF(LEFT(R6149,1)="+",RIGHT(R6149,LEN(R6149)-1),R6149))
What I was expecting is that under home 1 will be -05:00 and so on for the other homes.
Thanks
If the Time is Text then you can convert this using below formula then sum it:
=LEFT(A1,SEARCH(":",A1)-1)-(MID(A1,SEARCH(":",A1)+1,2))/69
Excel can't handle negative times, so these cells are presumably either text cells or use a single quote in front of the operator. You need to convert them to numbers before doing the math.
I would suggest separating the operator (+ or -) from the string, converting the hours to a number, and then recombining the operator and the hours. Because you're converting a time in hours to a number, it will be returned as a fraction of a whole day (e.g., 1 hour = 1/24 = 0.0416667), so you would multiply tit by 24 to get back to hours.
=(NUMBERVALUE(LEFT(B2,1)&NUMBERVALUE(RIGHT(B2,LEN(B2)-1)))+NUMBERVALUE(LEFT(B3,1)&NUMBERVALUE(RIGHT(B3,LEN(B3)-1))))*24
A better solution may be to convert all values to decimal numbers instead of times. You'd have to enter minutes as a decimal (e.g., 1 hour and 15 minutes = 1.25 hours), which could be tough for folks to do on the fly without a calculator, so you could provide a helpful conversion table from minutes to decimal hours. Timekeeping is often limited to some minimum interval, so the conversion table could be pretty short (e.g., if it's a minimum 5 minute interval, you only have 11 possible values for 0 minutes through 55 minutes).