Forum Discussion
Monthy budget planner starting on 15th...?
How about this:
Step 1: Identify the current cycle using cell A1
Assume A1 has the current date, e.g. 06/07/2025.
Use helper columns to calculate whether each item falls within the current pay cycle.
Step 2: Create a helper column (e.g., Column D) with this formula:
=IF(AND(DAY($A$1)>=15,
OR(DAY(B2)>=15, DAY(B2)<=14)),
IF(DAY(B2)>=15,1,IF(MONTH($A$1)<>MONTH(B2),1,0)),
IF(DAY(B2)<=DAY($A$1),1,0))This checks whether each debit falls in the current pay cycle based on the date in A1.
Step 3: Sum up the valid items
In your TOTAL cell, use:
=SUMIFS(A2:A10,D2:D10,1)This adds up amounts where the helper column equals 1 (i.e. debit belongs to current cycle).
Step 4: Color Code with Conditional Formatting
To highlight rows in red if unpaid, and black if paid, try this:
1. Select your rows (A2:C10).
2. Go to Home → Conditional Formatting → New Rule → Use a formula.
=$D2=13. Set the font color to Black.
4. Then add another rule:
=$D2=05. Set font color to Red.
This way, every time you update A1, your sheet will show you:
- The correct total
- Which bills are part of the current cycle
- Which are paid or unpaid visually
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
