Help

Re: Software Subscription Directory and Renewal Alerts

2663 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Ben_Frederick
6 - Interface Innovator
6 - Interface Innovator

I’m trying to create a table that houses all the information about the software subscriptions that I have.

  • Monthly Cost
  • Renewal Period (Monthly, Yearly, etc)
  • Number of User/Licenses
  • Next Renewal Date
  • Renewal Reminders

I’m running into trouble with Next Renewal Date and reminders. I’ve put in the start date of the subscription, which I use in a formula along with the period to calculate the first renewal date. But I want to calculate the next referral date in the future and have it update each time that referral date passes. How can I achieve this?

Formula: DATEADD({Start Date},{Period (Months)},‘months’)

2018-12-28_9-57-47.png

17 Replies 17

You need to split “Tools, Trainings, & Services” into two tables:

  1. One table called [Services] or something similar, where you list every type of tool, training, and service you provide.
  • Here is where you’d input the price of the service and any other static data that doesn’t change after each service period.
  • Add a Rollup field pointing to your new [Subscription Periods] table, described below, using the MAX(values) aggregation to get the latest start date and the latest calculated renewal date. This will update automatically every time you add a new “season”
  1. Another table called [Subscription Periods] or something similar, where you input every “run” of each service offered. (fyi, this table is what is often referred to as a “junction table”)
  • In [Subscription Periods] you’d make a Link to Another Record field that points to [Services] to identify the type of each season
  • Here you’d move your {User/Licences}, {Start Date}, {Renewal Date} fields. Now you won’t lose data on each period whenever you need to add a new one.

Thanks for the detailed response! I’m a little new to Airtable, so I’m going to have to take my time to work through this solution. But I’m assuming there’s no way to make this work by keeping it all in 1 table?

Ben_Frederick
6 - Interface Innovator
6 - Interface Innovator

I think I’ve figured out how to do this all in one table!

I basically created a field that calculates the amount of time between Today and the start date of the subscription in number of months. If that number of months is greater than the period of the subscription, then it adds that number to the start date to adjust for any prior renewal dates having past. If the difference of months between the start date and today is less than the renewal period, then it only calculates the first renewal date.

Renewal Date
Formula:
IF({Calculation for Renewal Date}>{Period (Months)}, DATEADD(DATEADD({Start Date}, {Period (Months)}, ‘months’), {Calculation for Renewal Date}, ‘months’), DATEADD({Start Date}, {Period (Months)}, ‘months’))

Calculation for Renewal Date
Formula:
(DATETIME_DIFF(TODAY(),{Start Date}, ‘months’))

Now, does anyone have any idea how I can:

  1. Remove the time from the renewal date.
  2. Remove the NaN from 2 fields when empty
  3. Remove the Error from 2 fields when empty

2018-12-28_14-11-54.png

Ben_Frederick
6 - Interface Innovator
6 - Interface Innovator

After testing out the above solution a few different ways, it all seems to be working right EXCEPT if the period is 12 or 24 months and it’s after the first renewal date.

In this situation it’s calculating the renewal dates to be earlier than they should be… Can’t figure out what I’m missing.

Ben_Frederick
6 - Interface Innovator
6 - Interface Innovator

How could I setup this formula?

IF ({start date} + {period months}) is less than Today, THEN add {Period Months} until ({start date} + {period months}) is greater than Today.

Is this type of formula even possible or is it circular?

Try this:
IF(IS_BEFORE(DATEADD({start date},{period months}, 'month'),TODAY()),DATEADD({start date},(ROUNDDOWN(DATETIME_DIFF({start date},TODAY(),'months') / period,0)+1)*{period months},'month'))

Thanks @Kamille_Parks :slightly_smiling_face:

I had to update a couple of referenced fields in the code before it would save it, so now the code looks like this:

IF(IS_BEFORE(DATEADD({Start Date},{Period (Months)}, ‘month’),TODAY()),DATEADD({Start Date},(ROUNDDOWN(DATETIME_DIFF({Start Date},TODAY(),‘months’) / {Period (Months)},0)+1)*{Period (Months)},‘month’))

Here are the results when plugging in this formula. A couple items have the same renewal date as the start date, some have renewal dates that are 2 month instead of 1 month ahead, and one has a renewal date in 2013.

image.png

Try:

IF(IS_BEFORE(DATEADD({Start Date},{Period (Months)},'month'),TODAY()),DATETIME_FORMAT(DATEADD({Start Date},ROUNDDOWN(DATETIME_DIFF(TODAY(), {Start Date},'month')/{Period (Months)},0)*{Period (Months)},'month'),"M/D/YYYY"))

Ben_Frederick
6 - Interface Innovator
6 - Interface Innovator

Here’s the results for that one.

image.png

So is that what you wanted? It looks like it worked to me based on your original draft IF() statement.

Well, only 2 out of the 6 records are showing renew dates and it looks like they’re incorrect. So I’m not sure what’s happening with that.

^ what you have in your base now is the correct syntax of the formula you gave above.

IF(DATETIME_DIFF(TODAY(),{Start Date}, 'months')>{Period (Months)},DATEADD({Start Date},(ROUNDDOWN(DATETIME_DIFF(TODAY(),{Start Date},'months')/{Period (Months)},0)+1)*{Period (Months)},'months'),DATEADD({Start Date},{Period (Months)},'months'))

Make sure “Use the same time zone (GMT) for all collaborators” is selected in the field customization options. If that’s not it then I don’t know what is.

Awesome, @Kamille_Parks! This fixed the problem I was having with the 12 and 24 month renewals. :slightly_smiling_face:

And it looks like MOST of the 1 month renewals are working correctly, with one exception. If a subscription with a 1 month period is between renewal #1 and renewal #2, something seems to be going wrong. Take a look at flowster. It was started on 11/27/2018 and it’s undergone 1 renewal on 12/27/2018. But the next renewal date is showing that same 12/27/2018 as the next future renewal date, but that date has already passed.

If I change the start date of flowster to 11/1/2018, the renewal date changes to 12/1/2018, which is still in the past. If I change the start date to 10/30/2018, it shows 11/30/2018 as the next future renewal date. But if I change the start date to 10/29/2018 or earlier (2 months ago or greater), the next renewal dates start showing correctly again.

Probably has to do with the way I described the formula to you.

image.png

Dirk_Jan_de_Wit
6 - Interface Innovator
6 - Interface Innovator

@Ben_Frederick @Kamille_Parks
Have you found a solution for your problem? I’ve a similar issue.
I’ve a member since field and I want that to update to next month, once that date has passed I want it to update to the next month etc…

It is working fine for some of the members that started in March, but the first renewal seems to be the issue.

I’m using this formula:
IF(DATETIME_DIFF(TODAY(),{Member since}, ‘months’)>1,DATEADD({Member since},(ROUNDDOWN(DATETIME_DIFF(TODAY(),{Member since},‘months’)/1,0)+1)*1,‘months’),DATEADD({Member since},1,‘months’))

Screenshot 2019-05-15 at 16.05.39.png

I’m pretty sure the problem might be Daylight Savings time, which happens around March and makes date calculations screwy in some instances. Not entirely sure how to fix it without more testing.

Labbielsen
4 - Data Explorer
4 - Data Explorer

To calculate the subsequent renewal date and set up reminders, you can use automation equipment reachable in many spreadsheet software, such as Microsoft Excel, Google Sheets, or Apple Numbers.
Here's how you can set up a reminder gadget for your software program subscription table:
Start by way of developing a column for "Next Renewal Date" and fill it with the system you have mentioned:
=DATEADD({Start Date}, {Period (Months)}, 'months')
Create any other column for "Days Until Renewal" with the formula:
=DATEDIF(TODAY(), {Next Renewal Date}, "d")
This method calculates the variety of days last till the subsequent renewal date.
To create a reminder system, you can use conditional formatting. In Excel and Google Sheets, you can go to "Conditional Formatting" and pick out "Highlight Cells Rules" and then "Less Than." Set the cost to "30" and pick a formatting fashion (e.g., purple historical past color).
This will spotlight any mobilephone in the "Days Until Renewal" column that has much less than 30 days ultimate till the subsequent renewal date.
You can additionally set up electronic mail reminders the usage of automation equipment such as Zapier or IFTTT. For example, you can create a Zap that sends you an electronic mail reminder two weeks earlier than the subsequent renewal date.
To set up a Zap, you may want to join your spreadsheet to the automation tool, create a set off (e.g., "when a phone is updated"), and then set up an motion (e.g., "send an email").
With these steps, you can set up a device that mechanically calculates the subsequent renewal date and reminds you when it is approaching.
Testing and web automation are two associated however wonderful fields in software program development.