Help with formula for working out billable hours

Hello, please could someone help me resolve a formula which isn't quite right. I would like to find out the number of billable hours in a given week based different figures for different projects. The formula doesn't work when the number of hours worked last week is greater than the difference between the quoted hours and the total hours worked to date. Here is an example.

Quoted hours: 35
Total hours worked to date: 42.5
Hours worked last week: 3
Billable last week: -4.5 whereas the figure should be 0. I am using this formula: =IF(D6>0, IF(C6<=B6, D6, B6-(C6-D6)), 0)

Many thanks,

Rob

your IF THEN formula is a little off. You want to have =IF(D6>0, do this, else do that.
if you have nested IFs then =IF(D6>0,do this,IF(C6<=B6,do this, else do that))
not sure if you want an AND or an OR in the first part or if you want nested ifs.
maybe this =IF(or(d6>0,c6<=b6),d6,b6-(c6-d6)),0) [not tested]?

EDIT: BTW, you need to change the title for your post per forum rule #1. Everyone needs help with excel formulas, not very descriptive. And a moderator will likely drop by to reinforce that.

@rjrfoster,

instead fo text, you get better help if you add an excel file, without confidential information.

Please also add the desirede result in you file.

there are too many statements in it. If something like what I gave you is what you want, this =IF(or(d6>0,c6<=b6),d6,b6-(c6-d6)),0) needs to change to this =IF(or(d6>0,c6<=b6),d6,b6-(c6-d6))
but beyond that, oeldere is right, it is hard to write a formula without seeing what factors you want to subtract or add. for example, I wrote this and it worked... =IF(AND(D6>=0,C6<=B6),D6,B6-(C6-B6))
but I have no idea which values you have in which cells.

Hopefully your title is ok now.
this appears to work.
=IF(AND(D3>0,C3<=B3),D3,IF(B3-(C3-D3)<0,0,(B3-(C3-D3))))

e3 =

Please Login or Register  to view this content.

and drag down