Jun 22 2021 05:35 AM
Jun 22 2021 05:35 AM
I am doing a worksheet for my finances. I have a column with my direct debits and the dates within the next month that they will be taken out of my account. What I would like to know is if there is anyway I can update these dates once the day has arrived and the money is taken out? For example, something comes out on the 2nd of July. Once the 2nd of July comes the date will automatically be updated to the 2nd of August. Can this be done?
Jun 22 2021 07:08 AM - edited Jun 22 2021 07:16 AM
@Tygra_1983 I would introduce a helper column with the day number of the direct debit and then use a formula to calculate the next due date.
A quick-and-dirty solution in the attached file.
Edit: Uploaded the wrong file. Corrected now.
Jun 23 2021 05:19 AM
Jun 23 2021 06:48 AM
Actually, the IF part of Riny's formula is a simple IF; it's the other parts that need explaining.
First, some of the references in the formula:
So here's how all that adds up in a simple IF function
Jun 24 2021 10:28 AM
Jun 24 2021 11:47 AM
You asked: how do I perform a calculation where once the value reaches zero it automatically resets to the original value. Like say there is a countdown from 14, once it reaches 0 it automatically goes back to 14? What formula would you use?
To which I have to respond, it all depends. There's not a single answer, unfortunately. It depends on how the data are arrayed, how that countdown itself occurs and how it's displayed. Is it a daily reduction by one, a reduction by one every time "X" occurs; is it even a reduction by one, or can the countdown be more random? You see what I mean?!
You'd probably need a helper column again, but as noted so very much depends on the rest of the process.
=IF(X=0,Y=14,Y=X) or some variation might work, but, again, it all depends.
Do you have a spreadsheet going where this needs to be resolved? If so, post a copy.
Jun 25 2021 05:24 AM
Jun 25 2021 11:42 AM
Jun 25 2021 12:10 PM
Jun 25 2021 08:00 PM
So what you really need (let me suggest) is a way to capture:
And THEN, based on those, a formula that calculates the days remaining, every month, for each of those payments.
If that makes sense -- if you'll be adding to this spreadsheet a more complete history, resembling a check register, then we can create a model for what that would look like and how it might work. Let me ask, though, before we devote time and energy to show you a possible design....whether that's what you plan. You've been coming here asking questions that are just a small part (I'm assuming) of what could be--should be--a much bigger project. Will you be wanting to add, say, budget categories to this effort to get on top of your finances?
Have you looked at the templates that Excel already has for check registers and the like? It's not necessary, and personally I often like to go design my own (for the learning it forces), but it would help me and others help you if you could tell us more about what your vision or hope is here with this start at a spreadsheet to manage your finances.
Jun 25 2021 09:37 PM
@Tygra_1983 Why not incorporate my original solution, with a few tweaks, into your own schedule. For every recurring item, you will need to enter the first due date. This will, in principle, not change. Then you calculate the next due date, similar to how I did in my 2nd post. Now you can calculate the days to go by deducting today's date from the next date.
See attached. Not that I created named ranges for cells C2 and D2. This is just to make the formulae that refer to these cells easier to read and maintain. Then, you'll note columns I and J. I've shaded these green to indicate that these are helper columns. Press the minus button above column K to hide them.
Jun 26 2021 07:16 AM
Jun 26 2021 09:27 AM
Could I ask you, to help both @Riny_van_Eekelen and me to help you, by spelling out a bit more fully the big picture here. Your big picture.
Although we can always give you an answer on how to do a specific formula, the more we know about the full context, what the big picture is, the more we can help with design and layout as well. Creating a budget spreadsheet, for example, or a check register, or whatever it is that you're trying to do...there will be multiple steps involved, multiple types of data you'll be collecting and organizing, and a clear goal in mind. The goal could be simply to make sure you pay all obligations on time; maybe that is ALL you're trying to do.
But even that involves several assumptions that you may be making. Like this 14 or 7 days request: WHAT is that really about? How does it bear on the "Start date" that you've carefully collected for each obligation? If it doesn't having any bearing, why enter it? Etc.
The series of questions you asked don't seem related to one another, which is why I'm asking. So I'm not sure that answering with a new formula each time is really helping when we don't have a coherent picture of the full purpose.
Jun 26 2021 11:24 AM
Apologies for not giving you a clear picture of what I am trying to do. At the moment though all I want to achieve is to have the information of dates when my direct debit or incomes come out. I just quickly want to see what dates my direct debits come out and a countdown in days to the dates. On my internet banking I need to know in my head this information, or write it down on paper which is more tedious. I just want to be able to open Excel and see "ah, my rent is due is 8 days, 7 days 1day etc.."
The furthest I have come by my own efforts is to create the formula "=IF(I4=C2,I4+14,I4)". I4 being the starting due date. C2 being =TODAY() and 14 being 14 days tell my next payment. However, this will only work for the first payment after the I4 starting date. So I feel completely stuck on how to fix this. Hence, why am using Riny's formula and asking how to change it for 14 days or 7 days.
Hope that helps.