Thread: how to sum negative times?

I am trying to set up a time registration for work. Now I've come pretty far but I have problems with some little or rather big formulas.
Let's say I have 2 cells. Each containing a time. The first contains my entire overtime and the second contains the overtime of the current day. Now of course it is no problem if the times are both positive. But if one day I work 1 hour less than I should, I have -01:00. I already found out a formula for the negative time, this is not the problem. But now I don't know how to continue. How to sum up this negative overtime. I think I have to work with an IF formula, since I have to cover each possible case (both positive, both negative, or one positive, one negative...)

I have tried a bit and the best I found for now is :

=IF(AND(H6>0;G7>0);H6+G7;IF(AND(H6>0;G7<0);TEXT((H6*24-ABS(G7*24))/24;"[hh]:mm");IF(AND(H6<0;G7<0);TEXT(ABS(H6)+ABS(G7);"-[hh]:mm"))))

assuming H6 is the total overtime and G7 the overtime of the current day. Now for both positive this works. But as you can see, I tried 2 different methods to handle the negative time. First is the *24/24 and the second (but I'm not sure if I used this one the right way) is the ABS formula.
Now of course I'm still missing the 4th case: IF H6<0 and G7>0 but I just couldnt be bothered to kill my time with this after the failure with the other formula.

Sorry I know this is a little bit complicating, but it's actually the first time I work with Excel formulas. I hope you understand my question, I know it might be confusing. But well... Thanks in advance for your help!!

No it doesn't work with just a simple addition.

I've attached the file now. It's a little bit complicated file, since it uses 2 other documents for the formulas.
It's in German, but the formulas are in English.. I hope it doesn't confuse you!
I know I still have a lot to work on there, but I hope you understand my problem and know maybe what to do!

Thanks

Thanks for this. You both helped me alot already with some formulas I wasn't exactly aware of problems.
@Canapone: I used the formulas for G and H as you suggested. But the problem I still have is that it doesn't sum correctly. It simply adds the value of the times. See, if I don't work one day, I get -7 hours "overtime". Before that I maybe had 10 hours overtime. So logically I should have 3 hours remaining. But your formula just adds the 7 hours to the 10 and then I have 17.

I just took some random numbers now to explain my problem. You see my problem is the H column. Do you maybe have an idea?

@6StringJazzer: I can't use the 1904 format since I have 4 other files that work with this one, so unfortunately I somehow have to find another solution for this problem.

Thanks alot

Thanks for this. You both helped me alot already with some formulas I wasn't exactly aware of problems.
@Canapone: I used the formulas for G and H as you suggested. But the problem I still have is that it doesn't sum correctly. It simply adds the value of the times. See, if I don't work one day, I get -7 hours "overtime". Before that I maybe had 10 hours overtime. So logically I should have 3 hours remaining. But your formula just adds the 7 hours to the 10 and then I have 17.

I just took some random numbers now to explain my problem. You see my problem is the H column. Do you maybe have an idea?

@6StringJazzer: I can't use the 1904 format since I have 4 other files that work with this one, so unfortunately I somehow have to find another solution for this problem.

Thanks alot

I really appreciate your help. I hope I'm not annoying you with this topic, it seems to be unsolvable for me.
My question is: Why can't I have negative cumulative hours? Is there no possible way? I mean, maybe it's possible to add a column in between where I convert the time into text, calculate it that way, and then in the next column (which would be equal to the H column now) I re-convert the (now maybe negative text) into time...
you know what I mean?

Thanks again!