Forum Discussion

Devon1990's avatar
Devon1990
Copper Contributor
Feb 07, 2024

Annual, Biannual, Monthly Calculations

Hello, I am trying to make a dashboard that will show which subscriptions I have coming up. What I need is to know how to calculate the next renewal date in order to better plan my finances. Just for reference, the original start date for the subscription is in column D, and the plan type is in column F (Annual, Biannual, or Monthly). The goal is that I don't want to have to update the start date in order to calculate the next period each time. It needs to automatically switch once a renewal date has expired, generating the next coming date. For instance, if today is Oct 7, 2024, and I started my subscription on Aug 4, 2022, the dates generated would be Aug 4, 2025 (Annual), Feb 4, 2025 (Biannual), or November 4, 2024 (Monthly). Can you guys help me on this kind of a thing?

excel
Formulas and Functions
  • rachel's avatar
    rachel
    Steel Contributor
    Feb 08, 2024

    Devon1990 

     

    Hi,

     

    I think the easiest is a while loop.

     

    e.g for annual, you can use 4 Aug 2022 + 12months + 12months + ... until the result hits "Today".

     

    I added a lambda to achieve this. (If you are using office365). 

     

     

    example spread sheet attached:

     

     

Resources