Help

Re: Formula for calculating end date of license

2203 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?

That’s basically how it’s setup in my “real” base. What I’m showing in here is just a small portion of it as it’s in Swedish :slightly_smiling_face: Creating a new record when a field hits a certain criteria was an interesting idea though! I’m trying to test it right now.

No, you can’t use formulas, but you can enter the same date that occupies the “End Date” field of the record that triggered the automation:

CleanShot 2021-09-07 at 13.12.36

CleanShot 2021-09-07 at 13.13.29

CleanShot 2021-09-07 at 13.13.45

CleanShot 2021-09-07 at 13.13.56

That’s a smart solution! Thanks! You don’t happen to know how to make the formula in my original question? :slightly_smiling_face: Kamille came close but the last date was wrong.

I wouldn’t want to try to figure that out with a formula – which is why I made the suggestion I did. It’s definitely a challenge, and I’m sure @kuovonne could figure it out in little time. But it’s not how I would approach the problem, so I’m not terribly motivated to try and figure it out.

The example given didn’t add up correctly to begin with, unless there was some alternate reasoning why the renewal date for 2022-02-01 is 2023-11-01 (21 months) instead of 2024-02-01 (24 months) when the listed {Length (months)} value is 24, not 21.

The formula isn’t that complex when you break it down, I removed it because it appeared this was already being worked on by a consultant and I didn’t want to undercut their business by answering a redundant question for free.

The example I gave you was wrong, your formula was right!

I think your solutions might be better. I’m trying to set up an automation but I can’t get it to work: Test are OK but nothing happens when I turn on the automation. Can you see why?

If you’ve asked a question in one thread on the forums, please don’t ask it again somewhere else.

Assuming your trigger is “When record matches conditions” or “When record enters a View”, if the record(s) in question already met those conditions/was in that view when the Automation was turned on, the Automation will not run for that record(s). From the Automation’s perspective nothing has changed therefor there was no trigger, therefor the Automation shouldn’t run.

You can cut and paste the date value back into the field to force the Automation to run.

Sorry about that! I’ve responded to your post in the other thread.