🏆 Upcoming Recurring Expense / Account Health Dashboard

[1Email2RuleThemAll]

Overview

What this workflow does

For this solution, you set up:

• Your income info via my Income Settings template.
• Some extra credit card metrics via my Credit Card Settings template.
• An inter-grain list of recurring expenses via my Recurring Expense Entry template.
• Install two new sheets:
  • Generated Expenses - Upcoming - a computational sheet that will be hidden
  • Upcoming Expenses - The dashboard that gives you all the neat data points below!

This solution then provides:

• Credit card totals due by your next paycheck
• A list of recurring expenses that are due by your next paycheck
• The total amount of money that those recurring expenses will cost
• The total amount of money currently in your checking accounts (latest balances pulled to Tiller)
• How much you will have left in those accounts after the above expenses & credit cards are covered
  • You can toggle this to see how much you would have left after paying all credit card debt, or just the totals that are due by your next paycheck.

Goal of this workflow

The goal is to easily see what all expenses will need to be paid before you are paid again, when they are due, how much money you have to pay for them, and how much you will have left after paying them. If the Savings & Debt solution is installed, this dashboard can also take into account how much money in an account is allocated toward a savings goal, and adjust the “available” amount accordingly.

This dashboard solves multiple problems:

• Track expenses on your pay schedule instead of being locked into a monthly beginning-to-end view
• Know if you have enough funds to cover your expenses until payday
• Keep track of how much money will be left over after your bills are paid
• Better forecast your cash spend and how much can be allocated to savings
• Track which expenses have yet to be paid, avoiding accidental late payments
• Track when credit card payments are due to better insulate against late payments/interest

Origins of This Workflow

I built the initial version of this sheet as a part of a custom spreadsheet solution in 2019, before discovering Tiller. I liked what Tiller had to offer, but absolutely needed a few of the sheets that I developed to feel like I had the same control over my finances as I had in the past. The dashboard offered here has been my “home page” for years, and I attribute a good deal of my financial stability to its insights.

I needed the ability to track my expenses on a deeper grain than monthly, which is the default for most Tiller templates. I like to know exactly how much money I have, how much I owe on credit cards, and how much I need to cover the recurring expenses that will be due by the time I get my next paycheck. So I rebuilt and refined my home page dashboard to work within the Tiller environment.

Custom Scripts, Formulas, and Additions

This workflow introduces 2 new custom sheets:

• Generated Expenses - Upcoming - A computational sheet to be hidden.
• Upcoming Expenses - The dashboard shown above, which can be renamed to whatever you’d like.

I decided to go with multiple sheets because I’m a fan of modular development. Doing this, I’m able to re-use part or all of my base sheets for other solutions.

Dependencies

The below required dependencies must be present. If they are not present, they should be installed and set up in the order that they appear in this document. If you already have one of my required community solutions, check to make sure that your version meets the minimum version requirements listed. If it does not, look for the instructions in that sheet’s documentation to upgrade the sheet before proceeding.

Required

Tiller Core Sheets

The following Tiller core sheets are required:

• Accounts - Used to pull back checking, savings, and credit card account info.
• Transactions - Used to determine if a transaction has been tagged to an expense, marking it paid.

Tiller Community Solutions

The following Tiller Community Solutions are required for full functionality of the dashboard. It will function with only the Recurring Expense Entry sheet installed (and Generated Expenses - Upcoming, introduced in this solution), but does not give the full picture of where you are financially.

Install in the order they appear:

• Income Settings - (min. version 2.0): Used to determine the next day you will be paid.
• Credit Card Settings - (min. version 2.1): Pulls in custom metrics calculated for credit cards.
• Recurring Expense Entry - (min. version 2.0): Used as the base that feeds Generated Expenses.
  • This solution has a dependency on the Credit Card Settings sheet, so install that sheet first.

Optional

Tiller Community Solutions

The following Tiller Community Solutions are optional, but highly recommended (for instructions on how to add/upgrade solutions after install, see the Version Changes - To Base Sheets section below):

• Savings & Debt - Used to see how much of the money in your accounts are allocated to savings goals in order to adjust your “available” amount accordingly.
• Make Savings & Debt sheet more exacting - This is just changing a few formulas in the Savings & Debt sheet to provide a more accurate allocation amount per month when you are actively funding savings goals. More information is present in the linked documentation.

Solutions that have a dependency on this solution

There are no current solutions that have a dependency on this solution

Installation

Dependencies must be present in your solution before installation. The Tiller core sheets should already be present, but you have to ensure that any required community built solutions present above are installed (click on the solution name to be taken to its install documentation). Afterward, the below steps must be followed in the order listed.

To install

1. Open the attached workflow
2. Add the Generated Expenses - Upcoming sheet
   a. Right click on the Generated Expenses - Upcoming sheet and copy to your Tiller workbook
   b. Go to your workbook and rename the sheet from Copy of Generated Expenses - Upcoming to Generated Expenses - Upcoming
   • Do not change the name of this sheet to anything else. It will break things.
3. Add the Upcoming Expenses sheet
   a. Right click on the Upcoming Expenses sheet and copy to your Tiller workbook
   b. Go to your workbook and rename from Copy of Upcoming Expenses to Upcoming Expenses
   • You can rename this sheet to whatever you’d like. It will not break anything.
4. Right click on Generated Expenses - Upcoming and hide the sheet. It does not need to be interacted with at all.

To Uninstall

• Right-click and delete the installed sheets above.

Setup

The only setup required is in installing/setting up the dependency solutions according to their documentation, and installing the two new sheets for this solution. Afterward, the dashboard should display the data you want in order to keep you on track!

Usage

Generated Expenses - Upcoming

The Generated Expenses - Upcoming sheet should be hidden, and does not need to be modified in any way. It dynamically generates the expenses that are needed for the timeframe that the Upcoming Expenses dashboard is looking at. This timeframe is one week prior to the beginning of the current month, up to when you are expected to get your next paycheck (calculated by the information provided on Income Settings)

Upcoming Expenses

After everything is installed and your info has been entered into Income Settings, Credit Card Settings, and Recurring Expense Entry, you can go to the report page and start viewing the data!

How it Works

Here, each expense that has yet to be tagged in the newly created Expense Tag column (introduced when installing the Recurring Expense Entry workflow) will be displayed in the field to the right, along with the budgeted amount for that expense. When a transaction comes into the Transactions sheet that is a recurring expense, use the drop-down in the new Expense Tag column to choose the expense that it is tied to. Once this is done, that expense will disappear from the dashboard.

• There are certain windows of time that each expense frequency is looking at in order to determine if it has been paid or not. If an expense is due weekly, for example, it will be marked paid and disappear as long as the posted date of the transaction that you tie it to in Transactions is within +/- 3 days of the expense due date. This is to avoid spilling over into the next expense window and marking things incorrectly. Each expense window is below:
  • Weekly: +/- 3 days
  • Bi-weekly: +6 / -7 days
  • Semi-monthly: +6 / -7 days
  • Monthly: +14 / -13 days
  • Bi-monthly: +/- 29 days
  • All Else: +/- 60 days

Any unpaid transactions will drop out of this list once the next month has been reached, except expenses due in the last week of the month, which will drop out the next month. The ‘view’ it’s looking at is basically a window of unpaid finances due a week prior to the beginning of the current month, up to the date of your next expected paycheck (if Income Settings is installed).

When the month ends, the list will automatically refresh to remove any unpaid expenses that was due in the last week of the prior month, and the first three weeks of the month we just exited.
When your next payday has been reached, it will change to the following pay date, and the list will automatically refresh to add the new expenses that will be due by that next pay date.

• If you see the below orange text insight pop up, the Income Settings sheet is not installed, or cannot be recognized by Google Sheets(this typically happens if the sheet was installed out of the specified order). If this is present, all expenses that are due by the end of the month will be displayed instead. To get the paycheck granularity view, install Income Settings (if it’s not already), then see the below section: Fix #REF errors arising from reinstalling a base sheet
  

For expenses that are present:

• The default text color is black
• The expense text will change color to orange when the due date of an expense is within three days, and change to red if the due date has passed and has not yet been tagged in Transactions. This is to give a visual que that action should be taken.
• The expense text color will be green if the expense is set up for full auto-pay, and purple if it is set up for partial auto-pay. This, again, is a visual que that you either do not have to worry about taking action, or that you should check that the partial auto-pay will go through as expected.
  • If the expense is set up for auto-pay, it remains green or purple until tagged in Transactions or falls out due to age, even if the due date has passed.
    

Text Insights

The text insights are there to give a plain-sentence, succinct description of the data. I find this to be very useful when thinking about the overall cash flow coming out of my various different accounts.

Checking Accounts

Up to 10 accounts, with a type of Checking on the Accounts sheet, will be displayed in the appropriate Checking Accounts box, along with the latest total of those accounts that was pulled into Tiller. In this section, there are 2 views that can be selected in the dropdown in column L2: Total Due & Total Pending.

• Selecting Total Due will calculate the sum of all expenses and credit card payments that are set to be paid out of each checking account by your next check, and the amounts left over afterward.
• Selecting Total Pending will calculate the sum of all expenses due by your next paycheck and ALL credit card totals, as well as the amount left over from checking afterward.
  
  

Savings Accounts

All accounts with a Type of Savings will be displayed in the appropriate Savings Accounts box, along with totals. If you have the Savings & Debt solution installed, this will also display how much of the money in savings is allocated toward savings goals, and how much is available after accounting for those allocations. Additionally, if you use a checking account for savings allocation, it will appear here as well, along with the amount allocated toward savings. The total amount in the checking account that is allocated to a savings goal will be subtracted from the total available for that account in the checking account section.

• If you see the below text insight pop up, the Savings & Debt sheet is not installed, or cannot be recognized by Google Sheets (this typically happens if the sheet was installed out of the specified order). If this is present, you will be unable to track funds that are earmarked for savings goals. To get this functionality, install Savings & Debt (if it’s not already), then see the below section: Fix #REF errors arising from reinstalling a base sheet
  

Credit Card Accounts

All credit card accounts that were set up on the Credit Card Settings sheet will appear in the appropriate credit card accounts box and display some handy totals and dates for you to have at a quick glance. The total of any upcoming expenses that are set to be paid with a credit card will appear in the last column of the section. The Total Balance for the credit card does not include these upcoming expense totals. Once an expense is put on a credit card, and that transaction is imported to Tiller, the Total Balance will update accordingly. Once you tag the expense on the Transactions sheet, the expense will disappear, and the total will no longer be pulled through to this column.

The chart shows credit utilization for each credit card present. I toyed with making this a sparkline, but they were displaying wildly inaccurate graphs for whichever credit card had the highest total. Something at 20% utilization was showing as more like 45% utilization.

• If you see the below text insight pop up, the Credit Card Settings sheet is not installed, or cannot be recognized by Google Sheets (this typically happens if the sheet was installed out of the specified order). If this is present, you will be unable to track upcoming credit debt due, or expenses set to be paid by credit cards. To get this functionality, install Credit Card Settings (if it’s not already), then see the below section: Fix #REF errors arising from reinstalling a base sheet
  

Notes

Upcoming Expenses

Missing Checking Accounts on Dashboard

The dashboard can display up to 10 checking accounts at a time. The accounts that are present by default are the first 10 accounts that have a type of Checking on the Accounts sheet, and are not hidden.

• The Account Type is imported from the financial institution. Unfortunately, sometimes institutions send over the wrong type for an account (usually setting it to “Other”). In this instance, a checking account that you want to see might not show up.
  • If this occurs, you can pull other accounts into the list using the Manual Inclusions column in the hidden section of the sheet (column W7:W12) until you reach 10 accounts.
  • To reach this section, click on the + symbol in the upper right hand corner
    
    • You will be able to select from all accounts that do not contain a type of “Credit” or “Loan”
    • You should only set checking accounts to be visible in this section.
    • All checking accounts that are set as pay methods for an expense or credit card should be present here.
  • You can also set the “Include 1st 10 Checking Accounts” option in column X2 to No in order to manually choose all accounts that are displayed.
    
    

Missing Savings Accounts on Dashboard

The savings accounts present by default are all accounts with a type of Savings on the Accounts sheet and are not hidden. If the Savings & Debt community solution is installed, this will also include any accounts that have savings allocations set up for them.

• The Account Type is imported from the financial institution. Unfortunately, sometimes institutions send over the wrong type for an account (usually setting it to “Other”). In this instance, a savings account that you want to see might not show up.
  • If this occurs, you can pull other accounts into the list using the Manual Inclusions column in the hidden section of the sheet (column W16:W30).
    • You will be able to select from all accounts that do not contain a type of “Credit” or “Loan”
    • You should only set savings accounts to be visible in this section.
    • You can also set the “Include All Savings Accounts” option in column X3 to No in order to manually choose all accounts that are displayed.

Inaccurate Credit Card Amounts Due

1. Make sure to read the Credit Card Settings documentation regarding the need to keep the prior close day column current for all credit cards. When one of your credit card statements end, you must change the Prior Close Day value to reflect the day that the most recent statement for the card closed, otherwise the Upcoming Total Due might be off.
   • Since there’s no real way to pull through statement balances to Tiller, I use the value entered into the prior close day column on Credit Card Settings to generate a close date in order to determine the sum of all purchases made after that date. I then subtract that amount from the total balance to get what will be due on the next due date.
2. There are a few instances where the total due coming up and total that will be due next due date will not be calculated correctly, even with the prior close day being correct.
   • If you use credit card rewards points to cover part of your statement, or if you get a refund for an item, for instance, the total due will be off. This has to do with the way credit card companies apply those types of transactions against the total, and how I calculate the totals.
     • When using credit card rewards, or when getting a refund, etc… the balance is first applied to the most recent transactions. This is opposite of when you make a regular payment, where the payment is first applied to any interest, then to the oldest balance.

Always make sure to cross check these numbers with your credit card issuer’s website.

FAQ

Please ask any questions you have below and I will reply, and update this section accordingly.

Solution Link

Upcoming Expenses Dashboard

Version changes

The initial release version of all sheets will be version 1.0. When a change is made, this number will increment. If the version number jumps to the next whole number, this indicates the need to reinstall the sheet using the steps outlined below. If not, it’s a relatively minor fix that can be applied by following the steps outlined in the appropriate documentation below.

To Base Sheets

When a base sheet has a minor change, it will usually not cause any issues in this sheet. If it does, a major or minor version change update in the below To This Solution section will indicate the steps necessary to take in order to get everything up-to-date. If a major update was made to a base sheet that required it to be reinstalled, some reference links could break in this sheet. To re-establish these references and get everything back in order, follow the below steps after reinstalling the base sheet.

Fix #REF errors arising from reinstalling a base sheet

This simple fix will also resolve any issues that would arise if you installed this solution before any of the sheets that it depends on.

• Note: Credit Card Settings is also used in Recurring Expense Entry, therefore this same process will also need to be done on that sheet if Credit Card Settings was missing or replaced. Refer to the documentation for Recurring Expense Entry for exact steps.

Upcoming Expenses

The Upcoming Expenses dashboard directly references these dependent sheets:
Income Settings, Credit Card Settings, Generated Expenses - Upcoming, Savings & Debt

1. Navigate to the reference columns in the hidden section of the sheet
   a. Click on the + symbol in the upper right hand corner, above column T.
   
   b. Click on the horizontal arrow to expand the hidden section of the sheet by column Y.
   
2. Make sure that the boxed section containing the sheet name does not contain #REF errors
   - For Income Settings, this is columns AC20:AF21
   - For Credit Card Settings, this is columns AH12:AK21
   - For Generated Expenses - Upcoming, this is columns AC10:AF17
   - For Savings & Debt, this is columns AH24:AK34
3. If #REF errors are present, double-click to enter into the cell containing the sheet name
   
   • Do not cut the entire cell, or this will not work. You need to double-click into the cell first
   • Press CTRL+A, then CTRL+X to cut the sheet name text from the box.
     • Conversely, highlight all text, right-click, and select Cut.
4. Press enter to save the changes and empty the cell. Double-click back into the same cell and paste what you just cut back in and press enter.
   • Once this is done, the workbook will have an “oh yeah!” moment and recognize the new base sheet is present. The #REF errors, and any issues you saw on the sheet, should be resolved.
5. Press the + sign above column T to collapse, and hide the remaining columns in the hidden section.

Generated Expenses - Upcoming

The Generated Expenses - Upcoming sheet directly references these dependent sheets:
Recurring Expense Entry, Income Settings

1. Unhide the sheet.
2. follow the same instructions above to cut/paste the affected sheet names back into the same cells to the far right of the page.
3. Hide the Generated Expenses - Upcoming sheet.

To This Solution

If you are jumping multiple minor versions, you can either apply all of the minor fixes individually, or re-install the sheet using the instructions below.

For versions that require a re-install of the sheet:
Remember to check any solutions that have a dependency on this solution, and follow their documentation for when these sheets are replaced.

1. Delete Upcoming Expenses sheet
2. Delete Generated Expenses - Upcoming
3. Follow instructions in the Installation - To Install section above

[1Email2RuleThemAll]

I’m linking all the people who I can remember speaking to who expressed interest in this solution. I finally got around to releasing it! Installing looks a little intimidating at first, but it’s really just “copy a sheet, rename to remove “copy of”, enter your info, repeat with the next sheet” for the most part. Once it’s set up, it’s a breeze to use, and super low maintenance. If you install, let me know if you have any issues!

@annaisakiwi
@hickson567
@jjfelton4
@mcmiller
@DDXdesign

[mcmiller]

Thanks a lot @1Email2RuleThemAll !

[annaisakiwi]

can’t wait to get into this!! thanks heaps!

[jjfelton4]

Woohoo!!! My little rinky dink sheet is getting an upgrade!

[YouBet96]

Very intrigued by this one as I’m thinking it may help me calculate and plan my floating month to month cash buffer that I keep in our checking. I may sneak back down here to my office during the festivities today and give it a whirl.

[dmetiller]

This is very interesting. If I’m understanding it correctly, though, it won’t really work with two different sources of income (i.e., my salary and my wife’s salary) due to the way the Income Settings sheet is set-up. Is that correct, or am I misunderstanding? Thanks.

[1Email2RuleThemAll]

Unfortunately, that is correct. Income Settings can only have one active income source at a time. But the only thing that this dashboard uses the Income Settings sheet for is to pull in the next pay date into the top right corner in cell Q1.
.

This date is used to display all expenses that are due by it. If you do not have Income Settings installed, this date will be the end of the current month. You could still track accounts, expenses and everything else the dashboard provides on a monthly basis if you wanted to give it a try. Or you could install Income Settings and set it up for one person’s income, then the expenses due would generate on that person’s pay cadence, giving you a closer approximation of your financial health.

[hickson567]

Can’t wait to use it!! Was out of town for the holiday last week. Thankful for this surprise in my inbox!

[randy]

As advertised, this new template from @1Email2RuleThemAll delivers:

• A list of recurring expenses
• Total cost of recurring expenses
• Available checking funds to cover recurring expenses
  While this solution is not “a budget”, it does improve clarity on aspects of expenses and income to improve your ability to budget.

This template is targeted at advanced users looking for full control of their analytics on their expenses and income with many granular settings and inputs driving the dashboard output. As noted in the instructions, three additional sheets are also needed to configure and power the Upcoming Recurring Expense dashboard:

• Income configured in the Income Settings template
• Credit card metrics in the Credit Card Settings template
• List of recurring expenses in the Recurring Expense Entry template

Particularly if you are already using @1Email2RuleThemAll’s other sheets, dropping this new template is a no brainer. If you are new to @1Email2RuleThemAll’s templates, just be aware the template isn’t a simple drop-in addition.

With our Builder Rewards Program, we offer cash awards for template submissions. Tiller is excited to award you $300, @1Email2RuleThemAll.

Thank you for sharing, for building such a customizable tool, and for documenting your creation so thoroughly.

[jjfelton4]

@1Email2RuleThemAll

Great workflow!! I’m loving the customization of these tools that work together! I’ve got one question on the Checking Accounts part. Specifically the Total Pending option :

Your explanation states that it includes the upcoming expenses due by the next paycheck, and the TOTAL amount of credit card balances due. I’m pretty sure I’ve answered my own question but I want to confirm, this is taking both cycle pay-dates into consideration correct? We always pay our cards when that specific cycle is due, so if this takes both cycles and combines them - in theory, if I’m paying that next card cycle off a month later, there would be 2 more paychecks that would occur between the time that is due.

Would there be a way to be able to look even more “into the future”, where we could potentially look at 2 or even 3 paydates out, or look at this as the Credit Card cycle due and then the current balance’s due date as well?

I suppose this sounds like a feature request, but if there is a way that I can modify anything on the backend, I’m happy to help!

Thanks so much for all of this hardwork! It’s incredibly helpful already, we really love the Recurring Expenses page, we just bought our first home and planning all of these One-Time expenses has been a life saver!

[1Email2RuleThemAll]

@jjfelton4

Great workflow!! I’m loving the customization of these tools that work together!
…
Thanks so much for all of this hardwork! It’s incredibly helpful already, we really love the Recurring Expenses page, we just bought our first home and planning all of these One-Time expenses has been a life saver!

Thank you! I’m glad to hear that this is already helping you better understand your finances! I’m sorry it took so long to respond! Work has been a little hectic this week.

@jjfelton4

Your explanation states that it includes the upcoming expenses due by the next paycheck, and the TOTAL amount of credit card balances due. I’m pretty sure I’ve answered my own question but I want to confirm, this is taking both cycle pay-dates into consideration correct?

You are correct. “Total Pending” shows the total of all expenses to the right that are set to come out of the corresponding checking account, plus the Total Balance amounts of all credit cards that have the corresponding checking account set as the payment method. This is how I use that option:

• Personally, I like keeping the total balance available in my account at a level that can pay for all of my current credit card debt, if need be.
  • If I’m applying for a line of credit or something, for instance, and want my credit score as high as possible, I want my total credit utilization to be as low as possible.
  • I also want to make sure that I’m living within my means, and not putting too much of my current expenses on revolving debt. That’s how you end up in a cycle you cannot get out of.
• The Total Pending selection allows me to see how much of that debt I could cover, and how much I would have left. If I select this option and I’m, say, -$163 available, I know that I can pay off most of the CC debt I have, but want to float about $200 or so until I get paid again.

If you only want to see the amount of credit card debt and expenses that are due by your next paycheck, you would select Total Due instead. I typically keep this option selected for the most part. But I do change it every now-and-then when comparing it against another report I built, which leads me to…

@jjfelton4

Would there be a way to be able to look even more “into the future”, where we could potentially look at 2 or even 3 paydates out, or look at this as the Credit Card cycle due and then the current balance’s due date as well?

This other report that I built does exactly this! Check Expense Forecast allows you to choose a starting check date, and then projects and generates up to 6 paycheck “lanes” that all have their corresponding expenses for that time period under them. It takes the expected paycheck amount (current Avg. Net Pay amount on Income Settings), then subtracts all the expected expenses for that period to give you an estimated amount available after they are all covered.

This sheet needs a little bit more work, though, to get it to where I want it before releasing. And it is kinda slow to update when you change the View Start Date (a second or two). This is because there are a lot of conditional formatting rules in play. But you should only need to change the date very sparingly, and it will not affect anything outside of that.

When I’m done, this report will have two views: Regular and Credit Card.

• The regular view will generate the expenses under the paycheck lanes that their due date is due.
• The credit card view will add the expense totals to the credit card statement balances of the cards set as the payment methods, and generate those totals under the lane in which that statement would be due.

Currently, I do not have the credit card view fully setup. Expenses that have credit cards set as the payment method are generating under the paycheck lane that the expense is due, not the lane that the credit card for that expense would be due. Once I work out how to properly do that, I will be refining and releasing it to the community!

[jjfelton4]

Personally, I like keeping the total balance available in my account at a level that can pay for all of my current credit card debt, if need be.
If I’m applying for a line of credit or something, for instance, and want my credit score as high as possible, I want my total credit utilization to be as low as possible.
I also want to make sure that I’m living within my means, and not putting too much of my current expenses on revolving debt. That’s how you end up in a cycle you cannot get out of.
The Total Pending selection allows me to see how much of that debt I could cover, and how much I would have left. If I select this option and I’m, say, -$163 available, I know that I can pay off most of the CC debt I have, but want to float about $200 or so until I get paid again.

This is all making sense now! It seems that our line of thinking is in sync while also different at the same time. I’m always thinking about the closed credit statement due, and then “adding paychecks” toward the next credit cycle in my head to forecast how I’m doing. And I’m not seeing some flaws in my own line of thinking from this. Your method is definitely the safer, more conservative, maybe even more responsible way to think about it. Which is what is so great about this app and community, I feel like I’m constantly learning about financial healthy & responsibility.

Now that I know your way of thinking for the Total Due vs Total Pending, it all makes sense and I’ll easily be able to adopt this method.

This sheet needs a little bit more work, though, to get it to where I want it before releasing. And it is kinda slow to update when you change the View Start Date (a second or two). This is because there are a lot of conditional formatting rules in play. But you should only need to change the date very sparingly, and it will not affect anything outside of that.
When I’m done, this report will have two views: Regular and Credit Card.
The regular view will generate the expenses under the paycheck lanes that their due date is due.
The credit card view will add the expense totals to the credit card statement balances of the cards set as the payment methods, and generate those totals under the lane in which that statement would be due.

This is MY way of thinking for the last part we were just talking about, it seems that you’ve just been able to define and organize it. I cannot wait for this sheet to arrive! Please tag me!

It’s now been a few weeks and I feel like I’m just getting comfortable with the all of the sheets and their interactions. I’m just having one slight hiccup with the setup, and that’s with the one time option on recurring expenses.

I’m having an issue with the charges not falling off of the “Due by next Paycheck” Chart, and I believe because of this it’s “double charging” me for these expenses, since they’re on both the upcoming and reporting on my transactions.

Above I have four upcoming expenses in red, all that I have paid, but I believe I only have 2 transactions actually tagged, the Plumber transaction will be updated today.

I want to show you every part to make sure I haven’t done anything on my end to cause this, I’ve read through your FAQ and tutorials multiple times to make sure I’m not missing anything.

Here is my ‘Recurring Expense Entry’ page entries, I believe you stated that One-Time expenses only needed a start date.

I wanted to show this transaction because this one actually did fall off of my upcoming expenses, while the two below have not dropped off yet.

Lastly, I have a screenshot of the’ Generated Expenses - Upcoming’ tab. I wanted to show this because it seems that the “Tm Technique” was correctly taken off because the 'Paid" column has been populated. While the others have not.

Got any ideas on how I can fix this?

Thanks!

[1Email2RuleThemAll]

Thank you for providing so much for me to go off of!

It looks like this is being caused by one of my formulas that I changed the week leading up to the release. It had some unintended consequences regarding behavior that I set for One-Time expenses elsewhere in the logic. It’s basically only looking for recurring expenses to be paid on the date they’re set to be due in Recurring Expense Entry, which is why the “Tm Technique” expense was marked as paid while the others were not. The Transaction date and the Recurring Expense Entry date match.

If you change the date that each of the other expenses is due on Recurring Expense Entry to match the posted date of the transaction you tagged, it will disappear. But I don’t want that behavior to persist where you have to do that every time. Follow the steps below to fix this permanently so that it will pick up the one-time transaction regardless of date paid (if it is +/- 90 days of the Recurring Expense Entry date). Just remember with the below fix, you can enter another One-Time expense called “Bed” or “Tm Technique”, but you would have to give the last expense an end-date to make sure things work correctly.

How to fix

• On the Generated Expenses - Upcoming sheet, unhide column A by pressing the arrow on the left side of column B.
  

• Double-click into cell A7, “ExpenseID”, and replace the code block with the below formula and hit enter.

={"ExpenseID", "Expense Name", "Frequency", "Exp. Amount", "Exp. Pay Method", "Exp. Start Date", "Exp. End Date", "Auto-Pay Set", "Exp. Master Pay Method";
    IFERROR(QUERY
    (
        FILTER
        (
            {
                 INDIRECT($AC21), INDIRECT($AC12), INDIRECT($AC13), INDIRECT($AC19), INDIRECT($AC15), TO_TEXT(INDIRECT($AC10))
                ,IF(INDIRECT($AC20)="", "Dec 31, 2999",TO_TEXT(INDIRECT($AC20)))
                ,IF(INDIRECT($AC16)="", "x",INDIRECT($AC16)), INDIRECT($AC18)
            }
            ,INDIRECT($AC17) = "✔", INDIRECT($AC10) <= $O$4, IF(INDIRECT($AC13)="One-Time", INDIRECT($AC10), IF(INDIRECT($AC20)="", "Dec 31, 2999",INDIRECT($AC20))) >= $N$4
        )
        ,"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9"
    ), {"","","","","","",""})
}

• Right-click on column A and hide it (optional, but looks better).

This should solve the issue! Please let me know if you run into any issues.

[jjfelton4]

Ha!

I even knew it has something to do with the exact date! I was honestly supposed to write about saying I think that might be it. I honestly somehow thought that I had already tried to make all of the transaction dates match, but I definitely missed it. But this makes it even better, bc in a real life situation (without someone really trying to make a formula stipulation work), a future expense will most likely be an estimate of the actual day/price of what will actually happen. This will be much better to not have to have them match!

Ok I’ll switch these formulas out, thanks for the quick response on this one!!

I’ll keep you posted on how it goes.

[kyle1234]

@1Email2RuleThemAll, this is a great sheet! Although, I have one issue, or perhaps I am not understanding how it works.

I get paid on the 1st & 15th of every month; however, I cannot get the sheet to move beyond “Due by next check on: December 15, 2023.” I was paid on 12/27/203 for my Jan 1 paycheck. If I manually update the 12/27 transaction to 1/1/24, it then updates my “Due by next check on:” to 1/15/24; however, if I manually enter a transaction on 1/15/24, my “Due by next check on:” remains as 1/15/24. Same results for February.

I tried setting Income Settings sheet to receive pay on 15|30, but get similar results…the Upcoming Expenses sheets gets hung on a paycheck that already happened.

The “Next Projected Pay Date” and “Current” table & cells hidden within the Income Settings mirror the behavior. My the Paydate updates to 12/27/23, but the Adjusted Pay Date reverts to 12/15/23 despite already having a paycheck transaction on 12/15/23. Playing around with future dates to troubleshoot – if I skip to 1/15/204, my Next Projected Pay Date stays as 1/15/204 until I enter a check on 2/1/2024, and so on.

I have tried a complete reinstall of your sheets in a completely new Google Sheet, but the results are the same. Any ideas?

[1Email2RuleThemAll]

I’m glad you like the workflow! I apologize you’re having some issues with the semi-monthly pay frequency. Looking at the code, I see the issue, though! This is a bug with the semi-monthly and monthly pay frequency logic in the Income Settings sheet.

I’m working on a solution to this now, though! I have to modify three formulas to get things in line, but it shouldn’t take long to do, but I’m also wanting to test and make sure it doesn’t mess anything else up! I’ll let you know when I have it worked out.

[1Email2RuleThemAll]

Ok, I have this working. The issue was that I was not accounting for semi-monthly users who are paid in the beginning of the month being paid early (making the pay date cross into the previous month).

I’ve resolved the issue, and a fresh install of the newest version of the Income Settings sheet (version 2.1) should fix the problems you were seeing!

Please let me know if this resolves the issue, and if you have any more questions/run into any more issues! And thanks for using my solution. If you like it, please consider voting for it at the top so others can be aware of it!

[kyle1234]

@1Email2RuleThemAll, it works, thank you! Just needed to fix #REF errors on Upcoming Expenses nd I am now good to go.

[1Email2RuleThemAll]

Yeah, that can happen when you replace a sheet higher in the dependency chain. I included some instructions at the end of the post for if that happens. I’m glad you got the #REF errors worked out!

I’m also glad that the fix works for you! Thanks for bringing that to my attention! It’s hard getting everything right the first time when you’re developing a single sheet to work with multiple frequency grains