Ability to create Negative Calculated Property Values (specifically for Time Between)

SOLVE
dvel
Participant

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!

1 Accepted solution

Accepted Solutions
Mike_Eastwood
Solution
Key Advisor | Gold Partner

Hi @dvel 

 

"Date Math" is tricky no matter what you use.

 

We build an integration to calculate dates for us (see our Birthday & Age HubSpot Intergation).

 

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.

 

Mike

 

 

View solution in original post

7 Replies 7
PamCotton
Community Manager

Hello @dvel 

 

Happy Friday, wanted to tag some top experts, @darynsmith@EmmaWashington @Josh what workaround would you recommend to @dvel matter?

 

Thank you,

Pam

Você sabia que a Comunidade está disponível em outros idiomas?
Participe de conversas regionais, alterando suas configurações de idioma !


Did you know that the Community is available in other languages?
Join regional conversations by changing your language settings !




0 Upvotes
sharonlicari
Community Manager

Hi @dvel 

 

Welcome to the Community!

 

This is an interesting case, I'm wondering what our experts have done!

 

Hi @Mike_Eastwood @johnelmer @JonPayne any advise?

 

Thanks

Sharon


¿Sabías que la Comunidad está disponible en Español?
¡Participa hoy en conversaciones en el idioma de tu preferencia,cambiando el idioma en tus configuraciones!

Did you know that the Community is available in other languages?
Join regional conversations by changing your language settings !


0 Upvotes
AngelaIke
Contributor

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.

Mike_Eastwood
Solution
Key Advisor | Gold Partner

Hi @dvel 

 

"Date Math" is tricky no matter what you use.

 

We build an integration to calculate dates for us (see our Birthday & Age HubSpot Intergation).

 

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.

 

Mike

 

 

View solution in original post

dvel
Participant

Thanks Mike! I didn't initially see the condition of "Date Property#1 is after Date Property #2", but that's actually exactly what I ended up doing.

JenNS1
Contributor

Hello HubSpot, can you please buy this plugin? 

Not sure why this isn't a native fuction. It's limiting. 

0 Upvotes
ChrisLuka
Participant

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.

0 Upvotes