Repeat for Annually, Quarterly between 2 dates

Steel Contributor

Hello experts,

 

I am not sure if I will get a response here but thought I would ask anyways to check if an expert has a suggestion/solution.

 

I have a file with due dates based on if the frequency is either annually or quarterly.  

If annual, use one formula (cell D4)

If quarterly, use another. (cell D5)

 

I want to repeat t for the number of "hits" for annually and quarterly between the Start Date and End Date.  I need the result to be in 1 column. 

 

I assume a solution would be in VBA code (some kind of loop..Im not a programmer).  Kindly see the attached file with sample data.   After viewing, I think you will be able to see more clearly what I am trying to do.   Sorry for any confusion but its better to look at the file to understand completely.  The difficult part to me is repeating only between the start date and end date.  

 

Grateful for your assistance

 

 

16 Replies

@Tony2021 

This can definitely be done via formula; no need for VBA.

 

However, it's not clear to me--your formulas make no reference to them--what the numbers 90 and 60 have to do with anything in here. If you can clear that up....

 

The other observation, though: you clearly--from the formula you HAVE written--are quite conversant with Excel and Excel functions. I wonder why you haven't tried just a little bit harder to write your own solution; it's much more satisfying.

 

I would point you in two directions, directions I'd go in to resolve your situation. One is the IFS function, whereby you could write a single formula that would give you your results whether annual OR quarterly. https://exceljet.net/excel-functions/excel-ifs-function

 

The other, more recently released (and needing the newest version of Excel), and more powerful, is the LET function. It can help make a formula more efficient where there are repetitions of major portions of a formula. https://exceljet.net/excel-functions/excel-let-function

 

Hi Mathes,

Thank you. I am interested in knowing how a formula would work for this. It would be better I think through VBA because of the start and end dates. The days should not really be a paid attention to because the formula refers to those days.

Even with IFS formula I don’t filly understand how that would be able to repeat from the start date to the end date.

To me this seems a VBA loop type of solution would be needed. But again I’m not a programmer.

Let me know if you have any other suggestions.

@Tony2021 

 

You wrote: "The days should not really be a paid attention to because the formula refers to those days."

 

What?!?!  That is a nonsensical statement. Since the formula refers to them, they HAVE to be paid attention to. So what is their function in the formula? Said another way, what is the connection between 90 and "Annual" or 60 and Quarterly? If anything, 90 is far closer to the number of days in a quarter of a year. Hence the confusion.

 

So please explain what the 90 and 60 are doing, why they're important.

 

And believe me, a formula is far and away the better solution.

 

While we're at it, if you really need a workday to be the answer in each case, that's fine. If I may, though, I'd like to  ask "Why?"  Is this for a highly legal financial matter and not just a personal reminder? If the latter, I should think that a far simpler formula would work, just adding 365.25 or 365.25/4 to each date  between the start and end dates. If you really need it to be a workday, as I said, that's fine. That does make it more complicated.

Mathes,

The 90 and 60 are the days after the annual or wtr and are referenced in the formula. I said the days are not important because the solution should refer to the formula (not the days). Yes, workweek is necessary. I am not looking to change the formula.

@Tony2021 

 

Try these solutions. I made a minor change to the layout, so that the start date appears above the column of scheduled annual payments. That way, each row going down refers to the date above in determining the next value. If the date that results from the formula is greater than the end date, then a blank is entered in the cell. This formula makes use of the LET function, and I'll explain it below. You will need the most recent version of Excel in order for this to work. Here's the formula

=LET(Intv,

90,

DueDay

,WORKDAY(EOMONTH(J4,MOD(12-MONTH(J4),12)) +Intv-1,1,HolidaysUS),

IF(DueDay>$E$1,"",DueDay))

 

The LET function first defines two things:

Intv which in the case of the annual schedule is the mysterious number 90 that you were unwilling to explain

DueDay, which is your formula modified ever so slightly to calculate the next payment date on an annual schedule

After which there is an IF function that simply says

IF DueDay is greater than the end date, display "", a blank, otherwise display DueDay

 

With Start date in Cell J4, this formula filled in in J5 and copied down, gives the annual due dates until starting to display blanks when the date exceeds the end date.

 

A similar formula, modified slightly for the quarterly schedule is in Column P

 

Now for some personal feedback: You're not an easy person to help.

  • You were unable to make a formula that worked and came asking for help. So by definition the formula had to change. Right?!
  • The formula refers to 60 or 90 and you're unwilling to explain what those numbers do. And yet you were the one asking for help!

I'm of the opinion that if someone, in an effort to help you, asks you a reasonable question that you really should answer it rather than blowing it off. I hope these solutions work for you. I enjoy solving problems like this because I learn in the process. I even like explaining how these functions work. It's even more satisfying when it's a cooperative effort.

@mathetes Interesting. I have never heard of LET. Could it somehow be adjusted to add functionality to simply drag the formula down for the entire dataset? What I mean is lets imagine there are 10,000 records with annually and quarterly spread throughout (which is the case but the example I provided was only a few records). Ideally, I would like to be able to drag the LET formula down 10000 rows and use the appropriate formula if the row is either "annually" or "quarterly" then use the corresponding formula (D4 or D5) but also being mindful of the End Date. 

 

Also, the 90 and 60 can be removed from the LET formula since I would refer to a cell instead of hardcoding, which would add more functionality.  Sorry it was confusing.  Kindly let me know if it is more clear now.  

 

The file I attached includes a simply modification (the 90 or 60 hardcode is removed from the LET formula and now refers to a cell in the row)

 

thank you for the help. 

 

 

 

@Tony2021 

 

I will have to look at this later. In the meantime, I really would appreciate it if you'd tell me what the 90 and 60 DO in the formula, why they're necessary, what function they perform? Etc.,

 

FWIW, I agree we should avoid hard-coding. You kind of made that necessary (if I may say so) by not explaining them. I'm not a financial person, although I do a lot with investing and have a pretty elaborate spreadsheet to track my options positions. What you have here appears to be somehow related to accounts with some kind of payment schedules...... I just like to understand WHAT the variables are, what they represent, if I'm to be writing a formula that involves them, whether or not they're hard-coded. What do they mean?

 

As to whether a single formula can be developed and copied down 10,000 rows, it surely depends in part on a number of questions. So let me ask:

  • WIll the start and end date be the same for every one of these rows?
  • Are the 10,000 rows representing 10,000 accounts, or 100 accounts with 100 payments each? Something in-between?
  • Why all on one huge sheet? Many people find it more useful to have a Dashboard that can show just the accounts for one person at a time, or something along those lines.
  • In essence, what all these are getting at: you seem to want to disclose a minimum amount of information, and maybe there are proprietary reasons for that, but it would really help--if we were doing this face to face it'd be easier--to know what the overall business is, what your overall data structure is. What the INPUT is; what the OUTPUT is. Where these 10,000 rows with one or two formulas fit into that overall structure.

The LET function is really a great addition to the repertoire of a spreadsheet creator. It was new last year. I've only had a couple occasions to use it. The one I created for you could be even more elegant, I'm sure. As I have been saying, though, it would be easier to do so if I had a better/clearer picture of the whole process into which this is fitting.

Again, too, here's a link to a good website where you could study up on LET: https://exceljet.net/excel-functions/excel-let-function

 

And I'm sure there are YouTube videos on the subject as well.

 

Mathes, the 90 and the 60 are the days after annually or quarterly.

To answer your queries:
WIll the start and end date be the same for every one of these rows?
==>answer: Yes
Are the 10,000 rows representing 10,000 accounts, or 100 accounts with 100 payments each? Something in-between?
==>Answer: the later....100 accounts w 100 payments each. Its not payments though but could be seen as such.

Why all on one huge sheet? Many people find it more useful to have a Dashboard that can show just the accounts for one person at a time, or something along those lines.
==>Answer: they all have to be on one sheet. I do have source sheets but still the annually and quarterly are mixed and wouldnt be easy to separate. Besides there are more cases and not only annually and quarterly. I have made it simple.

In essence, what all these are getting at: you seem to want to disclose a minimum amount of information, and maybe there are proprietary reasons for that, but it would really help--if we were doing this face to face it'd be easier--to know what the overall business is, what your overall data structure is. What the INPUT is; what the OUTPUT is. Where these 10,000 rows with one or two formulas fit into that overall structure.
==>Answer: Its not a payment schedule. Its a compliance report when certain deliverables are due (annually, quarterly...). I plan to adapt your solution to my real dataset and modify as needed.

Let me know if you have any other questions.

@Tony2021 

 

OK, Tony. I definitely have more questions. And, frankly, the more I think through this, the more it does appear as if a VBA routine may be needed. The fundamental problem in what you've described is that of borders or boundaries...how to recognize when one account (or whatever it is) ends and the next begins. It would be easy to recognize that a new quarterly or annual (or semi-annual or monthly) set of due dates is to begin. But because the number of rows will vary for each of those conditions, you need to have a way to provide exactly the number of rows that will be needed between start and end dates, and that would be a huge task NOT accomplished by just copying a single formula down 10,000 rows. 

 

To illustrate: in the example you've given you have a total of 20 rows, four for the annual due dates, 16 for the quarterly. So it would be easy to have a formula that deals with that already structured data set.

 

Are your 10,000 rows similarly set up, so that those that are annual for four years already have four spots, just needing the dates; those that are quarterly already have 16? If so, that's great, and we can write the formula. But if that structure---i.e., the setting aside of the requisite number of rows per _____________(account, client, security, option)--- is part of the task, then you do need a macro.

 

I have a formula in mind if the structure exists, but I am not going to write it out until I get confirmation that it will work. Maybe you could extend the example data to be more reflective of the true nature of what you're working with. Not all 10,000 rows, but maybe 6-10 different schedules (annual, quarterly, monthly) randomly assorted, if that's indeed how they appear.

 

And could I ask once again: You've said twice something to the effect "the 90 and the 60 are the days after annually or quarterly" but that doesn't tell me anything about why they're in the formula, what their significance is. It doesn't lead to understanding why we're dealing with them in the formulas. Is the actual due date 90 or 60 days earlier than the date the formula produces? Why is it 90 for annual and 60 for quarterly? What's the relationship? It's obviously clear to you; but you've not explained a thing of the Why? -- i.e., their PURPOSE in all of this. It may be so obvious to you that you don't understand why I don't understand..... I'm an intelligent person and I'm asking for a more comprehensive explanation to a novice in your field. I do understand Puts and Calls and can teach those derivatives concepts....so I doubt these are beyond my ability to grasp. Thanks.

 

 

@mathetes 

Hi Mathes, thank you for your reply. Seems we are making some progress.
I will answer your questions below:

 

Your Question: 
<Are your 10,000 rows similarly set up, so that those that are annual for four years already have four spots, just needing the dates; those that are quarterly already have 16?
==>Answer: Unfortunately, that is not the case. I use to have the ‘spots’ set up without the dates but I am doing another project and it was very tedious work to set up those spots.
Also, just FYI, the data set is not actually 10,000 rows. I was only making a point. Its more like 1000.

 

Your Question:
< is the actual due date 90 or 60 days earlier than the date the formula produces? Why is it 90 for annual and 60 for quarterly? What's the relationship? It's obviously clear to you; but you've not explained a thing of the Why? -- i.e., their PURPOSE in all of this.
==>Answer: for example, I need to produce a document 90 days after fiscal year end or 60 days. The days are simply the count. Sometimes its prior to FYE too. The 90 or 60 or whatever it might be, is hard coded inside the formula but it doesn’t have to be. I could reference a cell that displays the count (simply a cell with either 90, 60…) to make it more dynamic.

 

I think after viewing the additional sample data attached you might have a better understanding.

 

Let me know if you have any other questions. I appreciate your help.

 

@Tony2021 

 

Sigh....I think I'm as confused as ever. Maybe "perplexed" is a better word. If we were sitting down together, we'd be able to go far more quickly into some details that would be helpful. As it is, exchanging text postings, albeit some fairly long ones, we seem to be just scratching the surface.

 

If I were in your shoes, having whatever the responsibility is to produce these compliance reports, I would NOT want to be working with a list of even "only" 1,000 rows. I'd want to come up with a way--probably relatively easy, and maybe you're doing this--to highlight the reports due this month. I wouldn't want to have to go searching for them. I also see no real value in knowing today that on Tuesday, March 31, 2026 the reports of X companies are due, along with all the other due dates that are in the distant future.

 

I would seek, I think, to design a spreadsheet that shows me today:

  • the reports that are due (whatever: this month, this year).
  • Maybe also a summary of all reports by category (monthly, quarterly, annually; or accounting, construction, shipping...whatever the meaningful categories are) with frequency required and end dates....

 

Now, maybe you in fact are doing that. You're just asking here for help with a single (or several) formulas. But it's working with just a glimpse of the big picture that adds to confusion and frustration.

 

In any event, Tony, you clearly have a great deal of facility with formulas. I think this one in particular is a creative way to get at variability in whether a given report is monthly, quarterly or annual.

=WORKDAY(EDATE(E55,CHOOSE(MATCH(B56,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,1,Holidays_US_Jap)

I would suggest you could use the same idea via Named Ranges to store the values in a visible table somewhere in your workbook, such that values could be changed. Same for the 60/90/120/30/values for Timing, since those in particular could be changed, so having them in a table would obviate the need to hard code OR to put them in each row.

 

Let me share with you two** things that you might find useful, if not for this project, perhaps for something else calendar related. I've mentioned that I have a spreadsheet that I use for tracking options trades. Monthly Puts and Calls expire on the third Friday of each month. So here's a formula that will take any given year, any given month, and yield the exact date of the third Friday of that month. As you can see, it uses the LET function.

=LET(FstDa,DATE(L30,L31,1),

FstDa+CHOOSE(WEEKDAY(FstDa),19,18,17,16,15,14,20))

  • In that formula, L30 is a cell containing the year; L31 contains the month (both numeric values)
  • FstDa, then uses the DATE function to determine the first date of the month
  • The main formula then uses WEEKDAY to determine what day of the week that first day is
  • then CHOOSE to select various values depending on what day of the week that first day is
  • and then adds whatever value is "chosen" by CHOOSE to the first day of the month.
  • Recall, I use this as written just to determine the third Friday...now you can see that, if the first day of the month is a Friday, then the third would be found by adding two weeks, 14 days, to that first date. If, on the other hand, that first day of the month were to be a Thursday, then this formula adds 15. And so forth.

 

Whether or not something like that would be useful to you--since it enables the identification of given workdays in any month any year--is up to you to determine. I'm sure it could be made more powerful, more flexible, with a bit of tweaking (perhaps with MOD). I thought of it given (a) your need for a workday result, and (b) the need to project out due dates, falling on workdays, at specific intervals.   So I wonder if you could just have a database that shows:

  • each compliance report,
  • the date of the last report,
  • whether or not it's annual/quarterly/monthly,
  • and then add 12, 3, or 1 month to the date of the last report to yield the next date, the next Friday, one year out, three months out, one month out........

is it possible you could do away with the need for a 1,000 row (nearly unintelligible) table?

 

That's my question to you this time around. Is it possible for you to re-think how you approach this task?

 

**the second of two I'll post as a separate post. It's a spreadsheet that does some amazing things with dates.

@Tony2021 

 

Here's the second of two things for you to consider as ways to simplify your task of forecasting or planning the due dates for compliance reports.

 

The attached is a spreadsheet I got from some other source--I don't even recall where; it may have been here at techcommunity, although I don't think so. It's a table that can be used to lookup the Nth occurrence of any day of the week in any month of any year. Play around with it. It's potentially useful, in conjunction with the kind of reporting you have responsibility for. At the very least, it could be extended and used as the source of data for a VLOOKUP or XLOOKUP function.

Hi Mathetes,

Thank you for your insight into my project. Much appreciated.

To answer your questions:
<is it possible you could do away with the need for a 1,000 row (nearly unintelligible) table?
I have all the raw data in a sheet and I import it into access and I do filter for what is coming due.

To follow up on the formula
=LET(FstDa,DATE(L30,L31,1),
FstDa+CHOOSE(WEEKDAY(FstDa),19,18,17,16,15,14,20))
==>I am not sure how I could use it to be honest. I am not looking for the first, second, third MOn, Tues, Wed.... of the month. Its a date I am looking for that is the day count after or prior to the event that is due (ie Financials to be submitted 90 days past fiscal year end and the formula in the excel sheets I have sent also use the preceding (or following in some cases) business day if the day falls on a holiday.

The best solution to me is to have a formulas that would allow me to drag it down the 1000 or so rows. Or as you mentioned a VBA solution.

I am not sure if we can collaborate more on this or not but i am willing to.
thank you and looking forward to your comments. (note I do see your other comment below with the excel file attached)

@Tony2021 

 

You wrote: I have all the raw data in a sheet and I import it into access and I do filter for what is coming due.

 

This is the first mention of Access in all of our exchanges. Which underscores my earlier point that we just keep scratching the surface of this whole project. And that you import the raw data from Excel into Access!?! And then filter?

 

I am not sure if we can collaborate more on this or not but i am willing to.

 

I too am willing, but we can't just keep revealing small bits. I'm NOT a VBA/macro person. To the extent that might be what's required, I would not be the person to do it. To the extent that it's resolvable with functions, I'd be interested, but I would need to get a far more complete picture of where data is coming from (INPUT), how it comes into the picture, who's responsible for it; followed by OUTPUT--what really are the results needed? To just work on a formula, in the middle of what is clearly a major responsibility, a major business process flow with major consequences, isn't how to do it. It may be that in the end, all you need is a revised formula...or a VBA routine...but to just be shown the little part of it feels (to me, at any rate) like working blindfolded.

Hi Mathetes, sorry but I got very busy with my job. Maybe we can carve out some time to speak on the phone? I live in Houston, Texas. I assume I can send you a personal message possibly on this site with my phone no if agreeable.
I just sent you a personal message. You should see a notification up on the top right of your screen.