Hi, I am seeking a foundational template that will allow me to track my monthly budget from the 22nd - 21st of every month instead of 1-31. Will this be available any time soon?
Interesting request, @chelsi.troop. We don’t have many users tracking off cycle like this… so it isn’t a high priority at this time.
Depending on which templates you are using, it might not be super hard to update the queries to map to that time frame. If you are using many templates, this would be a big lift.
Which one/ones do you need to update?
I just use the foundation template and the tabs along with that. I would love if you could show me the work around.
I can give you some pointers on how to tweak the Monthly Budget (as an example)…
First, let’s ignore the problem of renaming the budget months. Let’s just setting on a convention that when the Categories sheet says Jan 2023 (with the value 1/1/2023), we are actually talking about the period starting on 1/22/2023. I think you’ll see that you can change this convention later.
Second, since we’re using the convention that Jan 2023 (for the period 1/22-23-2/21/23) in the Monthly Budget period selector maps to your Jan 2023 budget in the Categories sheet, we don’t need to make any changes to the budget information that is pulled in the Monthly Budget sheet. We just need to change the actuals that are queried.
Third, note that the selected period from the dropdown is rendered as a date (on the first of the month) in cell
Fourth, the actuals are aggregated in cell
V15. Within the
QUERY() function, you can see the date range:
...AND "&K25&" >= date '"&TEXT(K15,"yyyy-mm-dd")&"' AND "&K25&" < date '"&TEXT(eomonth(K15,0)+1,"yyyy-mm-dd")&"' ...
Essentially, the work is just changing the start and end dates in this part of the query to pull from a slightly different time span. A simple way to do this would be replacing the two references to
The solution to the Yearly Budget won’t be too different… but may require you to make the changes in more places.
I haven’t tested this, but it should get you pretty close. Let me know if it works.
Hi, could you please reinstate my trial on my account so I can see if this works?