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