Formula to calculate hours that are positive and negative

Hi.

I've spent the best part of this morning figuring out formula to get where I have for a timesheet to track my hours at work. I have managed to get everything working including getting negative hours to show without selecting the 1904 date system as it breaks other sheets in my workbook.

As you can see I have got my contracted hours and then the hours worked. This has calculated whether I am over hours or under hours each day. The part that I really can't get working is to calculate the total hours in that week as some days are positive hours so I have worked more than my contract and some days are negative hours so I have worked under my contract. I would like a formula to calculate the total weekly hours based on working more or less hours if that makes sense? I have tried everything that I know off and Google isn't getting the results I am after.

I have attached the workbook as well as two pictures showing what it looks like and the formula view.

I appreciate all your help. Have a good day.

Asda Wages.png
Asda Wages Formula.png

Originally Posted by CK76

Personally. I'd advise against using hh:mm with negative sign.
My preference is to display +/- duration value in either hours or minutes.
Ex:

Formula:

Please Login or Register  to view this content.

Then use standard arithmetic to calculate total up/down.

But if you need to calculate total as is. You just need to follow your formula construct and replace cell references with SUM(Column).

Ex:

Formula:

Please Login or Register  to view this content.

Hi.

Thank you so much for making it easy to understand. The formula "=IF(SUM(F7:F10)-SUM(B7:B10)>0,TEXT(SUM(F7:F10)-SUM(B7:B10),"hh:mm"),"-"&TEXT(ABS(SUM(F7:F10)-SUM(B7:B10)),"hh:mm"))" works perfect to get the total hours worked. I have tried to apply your response for each day based on if hours are under or over contract, but my response isn't coming out as expected even if I can the cell format.

I think for the basic needs I need, I think I may be alright with the formula to calculate the weekly hours of up and down. It would be nice to have a better display of daily up or down, but it's not too important as the result I get is sufficient. I was thinking of using conditional formatting or something else to get the daily up/down, but my knowledge on this isn't great. My excel knowledge is OK, but on this topic it's a little confusing.

Thanks again for your help.