Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Formula for calculating end date of license

3443 0
cancel
Showing results for 
Search instead for 
Did you mean: 
FortGjort_Trani
6 - Interface Innovator
6 - Interface Innovator

My sister can somehow calculate the value for the field End date in her head. Can someone please give me the formula that she is using, as I need to use a formula? :grinning_face_with_big_eyes:

https://airtable.com/shrWJArPUxWUy7HG2

18 Replies 18
DATEADD({Signed}, {Length (months)}, "months")

or use the following to avoid errors

IF(
   AND({Signed}, {Length (months)}),
   DATEADD({Signed}, {Length (months)}, "months")
)

That formula does not give the same dates as those in {End date}.

In what way is it different? How exactly is your sister coming up with these dates? If its just simple addition of “something” to “something”, you need to express what those somethings are so a proper formula can be written.

It’s not just a matter of adding something to something, unfortunately.

Let’s take the first license. It was signed 2019-11-01 and has a length of 12 months. Therefor it ended 2020-11-01. Since we are past that date, it was renewed for another 12 months, which gives us a (new) end date of 2021-11-01.

The second license was signed 2020-11-18 and has a length of 36 months. Therefor it ends in 2023-11-18. That date is in the future so that’s still the actual end date.

Perhaps it is not my place to say, but I would suggest, based on what you’ve said here, that you would benefit from modeling your data differently. I would suggest that you’d want the new license to be a separate License record, linked to the same Client record as the “original” License. You could differentiate the type of license (“Original”, “Renewal”) with a Single-Select field. Automations or Scripts could be used to automate the process of “renewing” a license as much as possible.

Structuring this way makes it much easier to make the sorts of date-based calculations you keep asking for, but in addition, it gives you the added bonus of being able to see a clear history of renewal per Client, statistics of how many times a Client has renewed, whether there were gaps in their renewal history, etc.

Thanks for sharing your thoughts, I really appreciate it. If I understood you correctly, you suggest that I use an automation or a script to create a new record when a certain date has passed? That sounds even more complex than what I’m struggling to do right now :grinning_face_with_big_eyes: I’ll look in to it.

Thanks!

Could I have the automation to create a duplicate of the old record but change a few fields?

That’s one possible way to handle it, and as you suggest, it does add a layer of complexity. For somebody that knows how to write automations or scripts, it probably wouldn’t be any more complex than writing a Formula that tries to handle dynamic dates, though.

But the main thing, the first thing I am suggesting, is to think of Licenses as records, one new record (row) per new license (whether the license is an original or a renewal of an original). And those License records can be linked to a Client record, one Client record per client you have.

Yes, it’s definitely possible to copy data from an originating record into the new record being created. I would suggest having a Linked Record field on the “Licenses” table that links back to another License, so that you can link a Renewal license to its original license.

I would urge you to focus on the data model (structure) first though. Establish a manual process by which you can create a new and renewed License record first, so that you know what your workflow looks like. Then, determine the constraints under which you would want to automate that process. You may find that you create an automation too hastily, and find that it is creating renewals when you didn’t want it to. To echo the old woodworking adage, “Measure twice, cut once”… “Plan twice, execute once”.

I couldn’t get the automation to set the signed field of the created record t0 DATETIME_FORMAT(TODAY(), 'YYYY-MM-DD'). Can’t you use formulas in the automation fields?