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