Updating Dates in Excel

Tygra_1983 

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.

Tygra_1983 

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.

---

mathetes wrote

 

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.

 

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.

 

 

 

 

 

 

 

---

mathetes 

Tygra_1983 

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.

mathetes 

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.

Tygra_1983 

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.

mathetes 

Wow, thank you Mathetes, the formula is much clearer now. I really feel I have learned something over this past week!

---

mathetes wrote:

Tygra_1983 

 

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:

Tygra_1983 

 

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?

 

Tygra_1983 

OK, here's that formula in column K: 

=IF(DATE(2021,MONTH($C$2),J6)>=$C$2,

DATE(2021,MONTH($C$2),J6),

DATE(2021,MONTH($C$2)+1,J6))

Other than the IF function (which is a simple IF in this case), there are only two other functions used:

DATE and MONTH

DATE works by creating an Excel serial number that represents the date. (FYI, Excel's serial numbers for dates begin with Sunday, Jan 1, 1900. The serial number for that date is 1. Days are added 1 by 1 since.) To generate a given date with DATE, you enter numbers representing a year, a month, and a day. 

MONTH takes a full date's serial number and tells you the number (1-12) of the month.

So putting that all together

• MONTH($C$2) takes today's date from cell C2 (or whatever date happens to be there) and returns the month
• and DATE(2021,MONTH($C$2),J6) takes the year 2021, the month from C2 (currently 6), and whatever number is in J6, to generate a date. In this case, because J6 contains a 20, the date is 6/20/21.
• the first conditional in the IF formula then just asks IF this newly generated date is greater than or equal to the date in cell C2 (i.e., does it still lie in the future?)
• THEN, the second clause in the IF function, since the condition is true, just displays that generated date 
• ELSE, the third clause in the IF function, since the primary condition is not true, i.e., the date has been passed, needs to generate the date in the succeeding month. So it uses MONTH to extract the current month and adds 1 to that -- MONTH($C$2)+1 -- and then uses DATE to put together that month, the year, and the day from J6, to show, for example, 7/20/21.

You know how best to start to figure these out for yourself? take a function like DATE and play with it. Put together different numbers (representing years, months and days, and refer to them in the DATE function. See what happens. Then embed a MONTH formula in there, and modify it using references that add or subtract values and see what happens to the date. It often is most helpful to go from the inside-out: in formulas like this, the MONTH function is embedded in the DATE, so first master MONTH, then build out to DATE.

Tygra_1983 

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.

It seems to be okay.    Good to hear.

 I'm not going to lie, I don't really understand the formula for the K column. I just about understand Riny's one, which is less complicated, and which you kindly explained.

I'll add an explanation when I get some time later on today or this evening.

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 updating days as well as months. Give an example of what you mean.

mathetes 

Hi Mathetes,

firstly, thank you for all your work on my behalf!

It seems to be okay. I'm not going to lie, I don't really understand the formula for the K column. I just about understand Riny's one, which is less complicated, and which you kindly explained.

So, is it not possible to create a formula where it updates in days as well as months?

Tygra_1983 

I took some time this morning to create one of the ways I would go about this. There is some documentation in the spreadsheet, in the form of text boxes, to explain things. Basically I've chosen to create a next due date column working off the day of the month (a single number) on which each monthly obligation is due.

Take a look and see if it works as you want. It does not use the 14 or 7 day thing that you described. Rather it goes back to simply figuring out the number of days remaining in the month.

The TODAY() field has been replaced with a simple entry of a day, so that you can test the functionality as if moving through the month of June. Once you're satisfied that it works put the TODAY() function back in place.

The formula could be made simpler IF you have the most recent version of Excel. There's a new function called LET that enables the elimination of unnecessarily repeating items in a formula. As noted, though, you'd need to have the most recent Excel for that to work. Let us know what version you have.

mathetes 

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 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.

Tygra_1983 

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.