Forum Discussion
Monthy budget planner starting on 15th...?
Hi Guys,
Thanks in advance for your replies.
I'm trying to find a solution to my spreadsheet problem to deduct my monthly direct debits from the total at the bottom, depending on the date.
My monthly pay cyckle runs run 15th of current month, to the 14th of the following month.
I have columns of amounts, date & name. I do not want to have to keep changing the months each month |(e.g. dd/mm/yy).
The idea is for me to open up my spreadsheet and when I change the date in cell A1, the sum total cell updates to show me the total that needs to be in my bank account to pay my bills.
| 06/07/2025 | ||
| Direct debits | ||
| Monthly | date | Name |
| £125.00 | 2 | Energy |
| £20.00 | 2 | Lottery1 |
| £18.00 | 8 | Lottery2 |
| £12.25 | 9 | Postcode lotto |
| £36.00 | 12 | Internet |
| £122.00 | 13 | Council Tax |
| £52.08 | 13 | Water |
| £64.53 | 16 | HP |
| £65.45 | 16 | credit card |
| TOTAL HERE | ||
I would like the total at the bottom to show the sum total of the direct debits that have not yet passed, according to what the date is in cell A1.
As my month runs from middle to middle (e.g. 15th Month1 to 14th Month2), if the date is the 16th onwards, it should add up the values between the 16th of month1 to 14th of month2. If the date is the 1st or 2nd, it should add up the values between the 1st or 2nd of month1 to 14th of month1. I would also like to change the colour of the cells to clearly show which values are included in the sum total and which are not, red for not yet paid and remain black for paid.
I hope this makes sense because I have ADHD & Autism and I struggle with communication. I am a beginner and I can manage basic excel sums and even stretch to adding percentages to totals, but that's it. I have no programming or coding skills, I am more of a hardware engineer with aging eye sight.
I have no idea what tags I would need to select at the bottom either.
Many thanks in advance,
Wavey75
6 Replies
Hi SnowMan55,
Thank you for your reply and solution. I have changed the date a few times and so far it works 100% as I wanted it to.
I’ll be 100% honest and admit I’ve spent quite a bit of my time looking at the formulas and the example and I don’t understand what it is doing – I think a fair bit of reading on my part is required!
I love it! Thank you so much for taking the time to work this out.
As a matter of interest, this solution you have provided, would it fall under basic, intermediate, advanced for expert level of excel? (I’m only asking so as I can work out where I’ll need to start learning and where I’ll need to get to.)
Many thanks to you and to the fantastic community of avid spreadsheet users in this community. I posted in another forum asking for this before this one, so I will link this solution to it so that the way to achieve this becomes more widely available to as many as who need it.
Wavey75
This solution would I suppose be considered an intermediate level of complexity. The IFS function is not commonly used, and the LET function is fairly new.
The important part is understanding the main cell formulas. To make it easier for you, I have added descriptive text in that regard into column C on the _Info worksheet.
I added a bonus column on the Example worksheet, and have added links to information about conditional formatting and custom number formats at the bottom of the _Info sheet.
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.
