Overclock.net banner

1 - 2 of 2 Posts

1,976 Posts
Discussion Starter #1
I have a budget spreadsheet I made for myself. The first three columns are: A: Date (every Friday) B: Beginning Balance C: Income

And then the rest of the columns are my recurring bills and expenses. Each cell under those columns have a negative number (-100 for phone payment, for example)

The next row down in column B, it uses a formula to add the income and subtract the bills and gives me what I should expect to see in my bank account the following Friday, before doing it all over again.

Anyway!! What I really want to figure out is this: I have this spreadsheet going all the way until the end of 2019 currently. Is there a way for me to easily take a bill that is due on the 20th of every month, and have excel automatically place it on the row that is the Friday before it’s due date?

Right now I manually take what I pay towards it, and put it in the cell for that bill. However, if I wanted to extend my spreadsheet another year, then that means I have to manually go through and put the subtraction in for every month again. It would be nice if I could say “this bill is due on the 20th, input -x on this column and every row where the date is equal to or within 6 days of the due date.”

I hope this made sense. Let me know if I need to clarify for share an example.


Not New to OCN
1,745 Posts
I think I've got a formula that works. I didn't test it for every possible case, but should at least be a good start. Basically it uses the DAY() command to check if the following week contains 20-27 for mortgage, and so on. Here is the exactly formula you need to put into cell E2 and then drag downwards:

=IF(DAY(A3) >= 20, IF(DAY(A3) < 27, -200, ""), "")

For the other case, we just modify the day value it searches for, and the deduction amount if the condition applies. in cell F2, then drag down, put the formula:

=IF(DAY(A3) >= 10, IF(DAY(A3) < 17, -100, ""), "")

The first number above (10) is the due date, the second (17) is simply adding a week, or 7 days, and lastly (-100) is the payment value. If the conditions don't apply, it will keep the cell blank. Hopefully with those, you can figure out how to do the others and add more as you need.
1 - 2 of 2 Posts