Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
May 30, 2021

Repeat for Annually, Quarterly between 2 dates

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 freque...
excel
mathetes's avatar
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's avatar
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's avatar
    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.

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

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

Resources