How to calculate balance when the date change

[jackson_hollon]

I need to calculate the balance based on the date change (G1). If the end date (column B) or the termination date (column C)is less than the last end month (N1), the balance should be zero, otherwise, it should show on column O the balance of the amount (column F less all the amount was used)

I need the formula be able to calculate the balance when the beginning date change (column G).

Thank you for the help.

[FDibbins]

Not sure if this is what you want?

=IF(OR(B2<$N$1,C2<$N$1),0,F2-SUM(G2:N2))

[jackson_hollon]

Not sure if this is what you want?

=IF(OR(B2<$N$1,C2<$N$1),0,F2-SUM(G2:N2))

Thanks for the reply. I want to calculate the remaining balance of the fee. For example: on row 5 column O, the balance should be 22500. If I change G1 to 03/01/2012, then O2 will change to 350, O3 is 4000, O4 is 8250 and O5 is 0.

Thanks for the help.

[jackson_hollon]

Can someone help here?

[sktneer]

Better you upload your workbook with two sheets "Before" and "After". On Before sheet you put your raw data and on sheet After you show the desired output explaining that how do you expect that desired output i.e. what value your target column O would have and why?

[jackson_hollon]

sktneer, Thanks for the reply. Attached is the excel showing 2 tables. The top one is when the table is set up, and the second one is the dates after changed, the balances will automatically changed with the right amounts.

[sktneer]

I have used the following formula. Check it and see if it fulfills your requirement.

Please Login or Register  to view this content.

[jackson_hollon]

Thank you very much for the help.

I have used the following formula. Check it and see if it fulfills your requirement.

Please Login or Register  to view this content.

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.