Ability to create Negative Calculated Property Values (specifically for Time Between)
SOLVE
I'm trying to calculate the difference between a custom Today's Date property and a custom Renewal Date property. Using this calculation it should be pretty easy to assess whether a client is considered Active or Expired depending on whether "Today" or "Renewal Date" is more in the future. The easiest way I could figure out how to do this was use the Time Between calculated property to calculate the time between the dates. The idea being that if the Today - Renewal Date > 0 a different custom property would be marked as Active, otherwise it would be marked as Expired. However, I noticed I could only get the absolute value (ie. positive) version of the calculation.
Eg. Today is Feb 4. Let's say we have 2 clients, one with a Renewal Date of Jan 4 (Expired) and one with a Renewal Date of Mar 4 (Active). The Time Between Calculation between Today and Renewal Date for both clients would show a difference of +1 month. This means I can't distinguish between a date that's in the past vs. one in the future which would be a very useful ability.
If there's a workaround for this calculation, I'd be happy to hear it. I reached out to the Support Team and it appears there's no ability to do negative calculated property values in general. There's a lot of useful ability that can be leveraged from having this ability so it'd be great to have this feature implemented!
We could adapt the integration for you to set a Renewal Date. Then you could create Lists with Renewal Dates using filters e.g. Renewals Next Month. Assign Tasks with a Workflow e.g. when Contact is added to Smart List send task to Contact Owner.
Or, you could add a Workflow that says "if Renewal Date > Today's Date" set a Custom Property to Expired.
Feel free to contact me directly if this could work for you.
Ability to create Negative Calculated Property Values (specifically for Time Between)
SOLVE
I'm actually working through this myself right now. The "good" news is that if you store the "days between" value in a property, it's actually internally represented as the number of milliseconds between the two dates, although the value is ALWAYS positive. You can divide that number by 86,400,000 (another calculation) to get the number of days, and then create a workflow that populates ANOTHER value as negative or positive depending on whether the two dates are before or after each other. I'm testing it now, and having SOME success, although it's a ridiculous amount of effort to simply compare two dates and get a meaningful result.
We could adapt the integration for you to set a Renewal Date. Then you could create Lists with Renewal Dates using filters e.g. Renewals Next Month. Assign Tasks with a Workflow e.g. when Contact is added to Smart List send task to Contact Owner.
Or, you could add a Workflow that says "if Renewal Date > Today's Date" set a Custom Property to Expired.
Feel free to contact me directly if this could work for you.
Ability to create Negative Calculated Property Values (specifically for Time Between)
SOLVE
I don't know if this helpful, but maybe you make two properties. One would be the time left as active, which is what you have today, and the other would be days expired. It wouldn't be the same property but this workaround might help you get where you need to be. From there, you might be able to create a third property and use a workflow to populate it based on the values of the other two. That's if you need it all to be in one property. I haven't tested this with your use case but I do something similar with properties we use to get things where I need them for reporting.