Forum Discussion
Updating Dates in Excel
mathetes wrote:
You wrote: firstly, thank you for all your work on my behalf!
You're very welcome. It's fun. I learn more myself by doing things like this.
I'm glad to hear that. I was hoping I wasn't trying your patience with my lack of understating.
mathetes wrote:
You wrote: firstly, thank you for all your work on my behalf!
You're very welcome. It's fun. I learn more myself by doing things like this.
So, is it not possible to create a formula where it updates in days as well as months?
I'm sure it's possible. But what do you mean when you say "updates in days as well as months"? From my point of view it is up dating days as well as months. Give an example of what you mean.
One of my direct debits is every 14 days. One is every 7 days. So I want them to update accordingly. At the moment they all only update every month. How do we change the formula for this to update after 14 days and 7 days?
It's more than just changing a formula. You have to include identifiers in the basic data that tell us what kind of frequency is needed for each payee. I was simply assuming that debits were monthly--which is the more common case. You've been asking about 14 or 7 day cycles, but I don't think had ever explained WHY you were asking for those cycles. Had you done so, we could have addressed it long ago.
I think that's the thing that you need to be aware of in designing a spreadsheet. Formulas/Functions work on variables that are supplied to them. Variables can be numbers, they can be characteristics (like monthly or weekly or.....) which may or may not get quantified. There are also constants. But it's up to the user to define what those are, and as much as possible to do that defining up front. It's in that connection that what might seem so obvious to you that you needn't state it, yet isn't at all clear to those of us on the receiving end, that we can spin wheels less productively. You also need to articulate a full process, full picture--so that someone who is attempting to help isn't just working on a formula "in the dark."
Anyway I've added a column to the spreadsheet for you to indicate "Frequency". I've entered a few examples, but am going to ask you to be very precise now in identifying which "Frequency" label goes with which payee. You'll see I've suggested four different categories, although there are more that could be possible. What I've started off with are:
- Monthly
- Semi-monthly
- Bi-weekly
- Weekly
You might also have some others, which could, for example, be
- Annual
- Semi-Annual
- Quarterly
And I'll note that Semi-Monthly and Bi-weekly are not synonymous. The former would lead to 24 payments during a year; the latter to 26.
The other thing that I've mentioned before, and will mention again--to be thorough in what you appear to be doing here--if I were in your shoes, I would want to have a record of transactions completed. In fact, for those payees that are on a 14 or 7 day cycle, I would even say it will be FAR EASIER to write a formula that tracks and notifies if you keep such a history. Then it would be a simple matter of adding 14 or 7 days to the last record for that payee (assuming that all such payments are made like clockwork.
Beyond that practical utility for the spreadsheet itself, a history of transactions would be like a check register; something that should a question arise you can point to and say "My records show that I paid XXX on [date]." A history of transactions could consist very simply of
- Date
- Payee
- Amount
Additional and potentially useful data elements
- Check #
- Confirmation # (if you use an on-line payment service)
Speaking of which--how do you make these payments? Is it by check or by on-line service? Can dates be any day of the week or do some/all have to be "working days" (i.e., not weekends)? Does it matter?
I'm not at all clear on the importance of Column I in all this--the "starting date"--other than as an item of historical note. It doesn't figure into any necessary formula at this point. But maybe there's some big picture reason why you want it, which is fine...but please spell it out.
Scott--this is going to have to serve for the time being, if the last one doesn't. IN this revision (which I don't consider really finished) I've given you a couple of formulas that should take you two weeks to figure out. Then I'll be back from vacation and we can connect via Zoom.
If these functions don't work at all for you it's because you need to update your Excel software (or subscription) to the most current version. I use a FILTER function and a new and very powerful function called LET. LET is really amazing, in that you can define variables once and then use them multiple times in the formula. I highly recommend looking for YouTube videos or other resources on how these two functions work. Here's a link to the best I've found on FILTER.
https://www.youtube.com/watch?v=9I9DtFOVPIg
LET I just figured out, but I'm sure there are videos that describe it....go searching.
Talk to you in two weeks.
- Sorry, I meant anytime you are free. It's night here now, so I will resume tomorrow.
Here's a revised sheet. It does use FILTER, a relatively new function, so if it doesn't work that means you would benefit from an update to your Excel software. If it does work that's great. Let me know either way.
- Right now I can't do that. I'm trying to send you a revised spreadsheet. Perhaps tomorrow. We're involved in a home renovation project right now that is taking a lot of time and energy.
mathetes wroteAgain: the bigger picture would be helpful. If we were sitting down together rather than exchanging typed messages, it would be a lot easier to develop an understanding. But you could help by being more complete, by being aware of assumptions you might be making that leave things far less clear than you realize.
Sorry, just quickly want to respond to this last thing you said. Would you be willing to do a quick zoom meeting? I mean hopefully it wont take longer than 15 minutes.
Just for the record, that earlier statement from you says nothing about one of them being due every 14 days and another every seven. It JUST says "I want to be able to open and see how many days until various debt payments are due." FULL STOP. The spreadsheet I delivered over the weekend did that, assuming monthly frequency, and nothing had ever explicitly suggested otherwise.
If you ever hope to program excel, you'll need to learn to be more specific and precise and comprehensive with yourself. Computers are specific and precise and literal. They don't read minds.
So now, just to be clear, you make 26 payments every year for rent. Is that right? Not 24. So there are some months in which, given the vagaries of things, you actually make three payments...on the 1st, the 15th, and the 29th. Or perhaps the 2nd, 16th and 30th. That's what payments due every 14 days would lead to. Is that correct?
Payments come directly from the bank, you tell me. Are they automatically debited by the bank on the pre-ordained schedule, or do you have to initiate each one? (If they're automatic, why are you creating the spreadsheet, one might wonder.) I pay all of my bills via direct payments from my bank. The standard and consistent ones--mortgage, in particular--are on a schedule that the bank adheres to. So I don't need to worry about making sure it gets paid.
So let me make absolutely sure I'm understanding what this is about.
- Are you expecting this spreadsheet to remind you to make payments? I.e, the payment won't happen unless you take action on or close to the designated due date?
- Or
- Is this just a way to be aware of cash flow, the outflow specifically, which is in fact all programmed and carried out by the bank?
Asked another way: are you an active participant in the ongoing payments--you initiate each payment-- whether in form of written check or bank-issued transfer of funds, or are you mainly a passive observer with overall accountability (to be sure) but just wanting to be aware of the net result of the flow of income and expenditures.
You didn't respond to my suggestion that a history of transactions be part of this. That in itself--the absence of a response--suggests to me that there's not a lot of interest. Adds to the impression that you're not that actively involved in completing the payments, that what you're wanting to create is more abstract. Is that the case?
Again: the bigger picture would be helpful. If we were sitting down together rather than exchanging typed messages, it would be a lot easier to develop an understanding. But you could help by being more complete, by being aware of assumptions you might be making that leave things far less clear than you realize.
- Are you expecting this spreadsheet to remind you to make payments? I.e, the payment won't happen unless you take action on or close to the designated due date?
I explained what I want to do back at the end of page 1:
"Hi Mathetes,
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 or 1day etc.." - (Depending on what day I open the spread sheet)
My rent is due every 14 days, my donation to the Clarendon Trust is every 7 days. The rest are monthly.
All the payments come directly out of my bank.
I have attached the spreadsheet with the frequency column filled in as you requested.