This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi everyone!
I created a budget sheet for my wife and I. All of our receipts are gathered and inserted into one "expense table". But just like most people, we have recurring expenses each month; such as rent, car payments etc. I was wondering if it would be possible to have a second table(recurring expenses) beside the expense table that can automatically be added to the expense table after that date has arrived. Its such a hassle adding recurring payments every month (they're easy to forget!).
Table Eg:
Expenses (random receipts) Recurring Expenses (rent etc.)
Date/Person/Category/ Subcategory/ Amount Date/Person/Category/ Subcategory/ Amount
The only difference is the date category. The expense category (date) is an actual selected date from a calendar (date/month/year), while the recurring expense (day) entry is a day such as the 1st etc..
I've been researching this idea but have had no luck. I feel my internet search description is hard to explain with one sentence. I've had no luck finding anything related to what I'm looking for.
Recap:
Expenses (random receipts) Recurring Expenses (rent etc.)
Date/Person/Category/ Subcategory/ Amount Date/Person/Category/ Subcategory/ Amount
- 3/23/2016***/*** Me***/*** Transportation***/*** Gas / $80.00 24th of each month*/** Me**/** Transportation /* Vehicle financing*******/*** $200
Question: Is there a way to automatically add the recurring expenses into the expense log every time that date has arrived (with the computers time)? I'd like to see this on the 24th(of every month):
Recap:
Expenses (random receipts) Recurring Expenses (rent etc.)
Date/Person/Category/ Subcategory/ Amount Date/Person/Category/ Subcategory/ Amount
- 3/23/2016***/*** Me***/*** Transportation***/*** Gas / $80.00 24th of each month*/** Me**/** Transportation / Financing /* $200
-3/24/2016***/*** Me / Transportation***/*** Financing***/*** $200
I know this is difficult but any help or suggestions would be appreciated!
Thanks for your time!
Justin
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
You don't need to enter the values from the right-hand table into the left-hand table - you can include those values by modifying your overall formulas.
Or you can have one table, with enough recurring entries (with formulas for amounts) at the top to cover the time period of interest, with non-recurring entries below:
Thanks Bernie!
I took a look at your example spread sheet. Very helpful! I'll just get rid of my recurring expense table all together.
Thanks!
Hey Bernie, thanks for your reply!
So I tried your solution but ran into some problems. After following your steps, this is what I ended up with:
Expenses (random receipts) Recurring Expenses (rent etc.)
Date/Person/Category/ Subcategory/ Amount Date/Person/Category/ Subcategory/ Amount
- 3/23/2016***/*** Me / Transportation***/*** Gas / $80.00 24th*/** Me**/** Transportation /* Vehicle financing*******/*** $200
(I forgot to mention "person, category, and subcategory" are all drop select lists with data validation { not sure if that helps} ).
I followed the steps to ensured all formulas are in the correct cells and that the cell links inside the formulas are in the proper spot. After I entered your formula in the cell below "date" in recurring expenses, I had a bold date with a double border on top of that entire table row.
Just so we're on the same page,
I'm looking to have a bunch of recurring monthly expenses in the right hand table that automatically get entered in the left hand table every time that date passes. I'm hoping that every time I open excel and the 24th passes, a vehicle financing expense of $200 is added to the left hand table automatically (just so I don't have to remember every time a recurring payment is).
I feel like we should have a formula that links the expense table with the recurring table at some point?
Thanks for your help!
In the recurring expenses, enter everything including the dates that the payment will be made for one month, such as 1/1, 1/5, 1/7 1/24, etc, but in the amount column, use a formula like
=IF(TODAY()>=H2,200,"Pending")
where H2 is the cell with the date of the payment, and 200 is the payment required. You can also return 0 until the date is reached:
=IF(TODAY()>=H2,200,0)
Then, in the cell below your last date, use the formula
=DATE(YEAR(H2),MONTH(H2)+1,DAY(H2))
where H2 is the cell with the first of your dates. Then copy that down until you get to December (with enough rows for all your December entries), then copy your descriptions and the amount formulas to match your new dates.