Forum Discussion
Monthy budget planner starting on 15th...?
Hi Kidd,
Many thanks for your reply - I'll admit this is WAY over my head.
I wasn't sure why your original solution was referring to the empty cell B2, so I assumed you meant it to refer to the first cell value I wanted to include, which in my example starts at cell B4.
I also wasn't sure if you meant that I should apply the formula you provided to each and every cell from D4 down to D12, so I did that and it refers to its opposite in column B (e.g. D5 refers to B5, D9 to B9, etc)
When I change the date to 01/MM/DD, the value of all the D cells goes to 0, which shouldn't be the case.
I colours thing, that's proving to be a right bugger.
I get these boxes up when I try to add the new rule you suggest and I don't know what to do about it.
(I wish I could just upload the excel file here,) Any ideas?
If it's allowed, I've uploaded the file to my public google drive and hopefully, this link will show:
https://docs.google.com/spreadsheets/d/1_xnMxF0LLnMv3OWNh897l0cMg8Xs4xI7/edit?usp=drive_link&ouid=101246562292737372264&rtpof=true&sd=true
Many thanks!
The conditional formatting (CF) rule should instead be the one where you always specify a formula (at least in Excel; I would guess Google Sheets supports that also).
See the attached workbook for my solution, explanations, an assumption, and reference material.
Hi Wavey75
Please check the working solution for the monthly budget planner that runs from the 15th of the current month to the 14th of the next.
I just enter the current date in cell A1, and everything updates automatically.A - Amount B - Day (number) C - Name D - Full Date (calculated)
[b]Reference Date (A1)
Enter today’s date. Example:
2025-07-07[b]Cycle Start (D1)
=IF(DAY(A1)<15, EDATE(DATE(YEAR(A1), MONTH(A1), 15), -1), DATE(YEAR(A1), MONTH(A1), 15))
[b]Cycle End (E1)
=EDATE(D1,1)-1
[b]Full Date column (D4 down)
=IF(B4>=15, DATE(YEAR($D$1), MONTH($D$1), B4), DATE(YEAR(EDATE($D$1,1)), MONTH(EDATE($D$1,1)), B4))
This assigns a full date to each row based on the 15th–14th cycle.
[b]Total for unpaid direct debits this cycle
=SUMIFS(A4:A12, D4:D12, ">="&A1, D4:D12, "<="&E1)
[b]Conditional Formatting (applied to A4:D12)
[i]Unpaid (Red)
=AND(D4>=A$1, D4<=E$1)
[i]Paid (Green):
=AND(D4<A$1, D4>=D$1)
[i]Outside Cycle (Grey)
=OR(D4<D$1, D4>E$1)
Now when I change the date in A1, everything updates automatically:
- Red = unpaid and still due this cycle
- Green = paid during current cycle
- Grey = outside this cycle
- Total updates based on remaining unpaid items
