I need help with an Excel formula dealing with negative numbers?

Question

I'm working on a project that tracks a person's time off balance.

The formula I'm using is =A3-B2+B1 where A3 is the starting balance, B2 is the time used and B1 is the accrual rate that is to be added in monthly.

Example: Someone with a balance of 17.334 hrs uses 5.2 hrs of time off giving them 12.134 hrs and then gets a monthly addition of 6.334 hrs with the final total of 18.468 hrs.

So here is the problem, when a person used more time than they have, they get a negative number for the balance and not a 0. When the next accrual is put it, the new balance is wrong because of the negative numbers.

Example: someone has a balance of 7.5 hrs left and uses 9 hrs then gets 6 hrs of accrual. Excel shows 4.5 hrs but for payroll purposes, it should read the full 6 hrs.

I need a corrected formula to put into these time sheets so that when the balance is <0, it will read 0 and then the accrual will be added in full. I'm not sure how to do this. Any help would be great.

Answer 1

you could put an IF statement in. saying IF(a3-b2+b1>0,a3-b2+b1,0) that way if its more than zeor, it'll be the value. if it's 0 or less, it will still be 0.

Answer 2

=If(a3-b2 <0, then 0+b1, a3-b2+b1)

Answer 3

Wouldn't this work?

=IF((A3-B2)<0,B1,(A3-B2+B1))