May 30 2021 04:30 PM - edited May 30 2021 04:40 PM
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
May 30 2021 05:41 PM
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
May 30 2021 05:54 PM
May 30 2021 06:03 PM
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.
May 30 2021 06:24 PM
May 30 2021 07:28 PM - edited May 30 2021 07:28 PM
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.
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.
May 31 2021 11:16 AM
@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.
May 31 2021 12:01 PM
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:
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.
May 31 2021 12:44 PM
May 31 2021 04:56 PM
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.
May 31 2021 07:21 PM
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.
Jun 01 2021 07:50 AM
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:
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))
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:
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.
Jun 01 2021 08:00 AM - edited Jun 01 2021 08:08 AM
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.
Jun 01 2021 08:30 AM
Jun 01 2021 08:53 AM
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.
Jun 02 2021 05:19 AM
Jun 02 2021 06:24 AM