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

Tony2021 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.

Tony2021 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.

Tony2021 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 explainDueDay, which is your formula modified ever so slightly to calculate the next payment date on an annual scheduleAfter which there is an IF function that simply saysIF 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.

Repeat for Annually, Quarterly between 2 dates

16 Replies

mathetes
Silver Contributor
May 31, 2021
Tony2021
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
- Tony2021
  Iron Contributor
  May 31, 2021
  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.
  - mathetes
    Silver Contributor
    May 31, 2021
    Tony2021
    
    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.

Forum Discussion

Repeat for Annually, Quarterly between 2 dates

16 Replies

Resources