Forum Discussion
Repeat for Annually, Quarterly between 2 dates
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.
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.
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.
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.