Repeat for Annually, Quarterly between 2 dates

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.