Excel Formula Assistance

[richy22]

Im having an extremely blonde day today and just cannot get my head around why this formula is incorrect.

I have made a spreedsheet that tracks TOIL for us here at work but I hit a snag when a MINUS number of hours are added into my cell.

Formula is this in cell Columns I...=SUM(F4-G4+H4)............This cell is formatted with [h]:mm as I need the TOIL to be shown in that way

Cell F4 = Monthly Total of hours formatted [h]:mm
Cell G4 = Hours should be worked that month formatted [h]:mm
H4 is Balance brought forward. Now this last cell is also formatted [h]:mm and works fine until a minus figure needs to go in that. I have to do '-16:30 to get the minus hours in there and then get an error in my calculation #VALUE!

Now I can get the formula to work by changing cells in H and I to GENERAL but then the resulting calculation is displayed as say 16.75 hours which is not how I want it displayed.

Anyone able to shed some light???

 

[richy22]

Fcuk it I played around and came up with a "solution"

Made an extra column of "hours" that is the negative column. So I have 2 columns for TOIL hours from previous months. One for positive one for negative hours.

Then made a formula that says if its in that column to minus it, if its in the positive then add it ... =IF(H27<>"",J27+H27,J27-I27) Appears to work so all is good and displays in the format hh:mm which is what I want.

That'l do pig

 

[The Boosh!]

Rich - I think i've understood you correctly...

If you want to do it with out faffing about with extra columns etc, it is possible.

Is the attached what you were looking for?

​View attachment Book1.xls

If so I will talk you through what the crack is.

​

 

[SC03OTT]

Do you not have to 'tell' your formula the negative number should be read as an absolute value?

 

[The Boosh!]

Do you not have to 'tell' your formula the negative number should be read as an absolute value?

You can stick =abs(formula) in front of the sum that the OP has posted, however it's a bit misleading as not easy to identify who has a positive and who has a negative balance.

 

[richy22]

Cheers for the post Boosh..

its a bit more complicated then that though.

I have 5 Sheets on the workbook. First 4 are weekly time in and outs split into days.

Important stuff is on last sheet to do with monthly totals. It takes the data from sheet 1 and populates that cell (formatted [h]:mm)

Does this for the 4 weekly totals in columns B, C, D, E

Column F = =SUM(B4:E4)

Column G ="37:30"*4 to give hours should work.

Edit sod that here is a rough example if dropbox works

https://www.dropbox.com/s/th5okhfopeku2fl/TimeSheet.xls?v=0mcns

Only have 1 week on there usually I will have 4 and then the sums mentioned above.

I used to do it by just having one coumn for TOIL "H" and then do this in difference =SUM(F6-G6+H6) but if any negative value was added into column H I would get a #VALUE! error due to having to put a single quotation in that cell to get a negative number.

 

[The Boosh!]

Doesn't work mate.

 

[TimR26]

I've got a timesheet which will do the job. I can send it to you when I'm back at work on Tuesday.