Our monthly budget cycle doesn’t run on a calendar month, it runs from the 20th of the month to the 19th of the following month. I’ve researched the posts and the last one that was posted was in 2023 and the cells referenced no longer exist in the current foundation template so I’m hoping someone can help me think through how to update my spreadsheet for it to recognize that the “January” monthly budget actually runs from 12/20/25 to 1/19/2026. TIA!
I don’t think this is impossible
Have you tried changing the months start date in the categories sheet? They are formatted to appear as month date but they actually are a date.
I think that may be a solution! Going to work on that and I’ll report back!
Probably won’t work on its own, need to adjust the Query function in hidden cell w15 to have the actuals for the month with the displaced dates and the start date in K15.
I will give it a look in the next few days but hopefully somebody can solve it a bit faster!
I would be super grateful! Is that hidden cell w15 on the Categories tab?
No on the monthly budget sheet
I think this should work.
- In the monthly budget sheet, in the hidden cell K15, change the formula to
=date(H2,MATCH(H3,{“January”,“February”,“March”,“April”,“May”,“June”,“July”,“August”,“September”,“October”,“November”,“December”},0),20)
The 20 would be the day you want your budget to start. Change it to whatever day you need your first day of the month to be.
- In the monthly budget sheet, in the hidden cell V15, change the formula to
=iferror(query(Transactions!A:AD,“SELECT “&K26&”,SUM(”&K27&“) WHERE “&K26&” IS NOT NULL AND “&K25&” >= date '”&TEXT(K15,“yyyy-mm-dd”)&“’ AND “&K25&” < date '”&TEXT(eomonth(K15,0)+19,“yyyy-mm-dd”)&“’ GROUP BY “&K26&” LABEL SUM(”&K27&“) ‘Actuals’”))
The +19 following the eomonth function is the day you need the budget month to end. That would normally be 1 day less than the date you put in the formula in V15, otherwise there would be a gap or an overlap.
That way by selecting January in the dropdown menu in H3, the January month actuals would be pulled from the transactions sheet from January 20th to February 19th.
Give it a try and let me know.
I was almost there, sorry.
Put this formula in K15 if you want it to start on the 20th of the previous month to the 19th of the month that is selected in H3.
=edate(date(H2,MATCH(H3,{“January”,“February”,“March”,“April”,“May”,“June”,“July”,“August”,“September”,“October”,“November”,“December”},0),20),-1)
I really, really appreciate your help thinking through this. I went and changed both of those formulas in the Monthly Budget sheet and unfortunately I’m still getting errors in both cells. I’m not sure if this would impact it (I wouldn’t think so) but my current selections are the year 2025 and month of December (so the goal would be to have the timeframe of 12/20/25 to 1/19/26. Let me know your thoughts.
1 Like
This is a good question, @ehpropmgmt. Honestly, the templates weren’t designed with this functionality as part of the specification.
I think you will see some formulas that DO aggregate data using specific DAYS in their start and end ranges but I think you will also find many places where, for expediency, there are shortcuts (e.g. EOMONTH or QUERY aggregating with MONTH functions) that aggregate monthly data more coarsely.
There is really no reason this can’t be made to work by stepping through the formulas and the incremental calculations in a template that is important to your workflow, but I’d just caution that the implementation (on templates developed over ~10 years) is somewhat inconsistent in its suitability for this workflow so you will need to be methodical in reviewing and updating formulas.
1 Like