CRM

dvel
Participant

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!

1 Accepted solution
Mike_Eastwood
Solution
Key Advisor | Gold Partner
Key Advisor | Gold Partner

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

SOLVE

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

10 Replies 10
Franci
Top Contributor | Diamond Partner
Top Contributor | Diamond Partner

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

SOLVE

Hello 🙋🏼‍

 

If you have ever struggled with calculating future or past dates, say hello to the Workflow Date Calculator app we have released!

 

Key Features:

 

- Set Missing Days: Choose your start date, select your target property, and enter the desired number of days/weeks/months.

- Compatibility: Works seamlessly with HubSpot's standard objects—contacts, companies, tickets, and deals.

 

Install the App: Head to the HubSpot Marketplace, install the Workflow Date Calculator, and get started for free.



Say goodbye to manual date calculations and hello to efficiency. 

Try the Workflow Date Calculator today and elevate your HubSpot workflow game!

Francesco Collacciani

HubSpot Specialist @ Exelab

#1 HubSpot Diamond Partner in Italy

emailAddress
francesco.collacciani@exelab.com
website
https://www.exelab.com/en/
Get in touch with us!
DStaat
Participant

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

SOLVE

Hey @dvel,

There's an app call Tick Tock Today that could help with this! It creates and updates a today's date property on your object records. 

TTT Demo.png



Using this, you can create a time between property. I covered how to do this in this blog post

You can check out the app on the HubSpot Marketplace here. I'd also be happy to hop on a call with you to help set it up!

Best,

-David Staat

0 Upvotes
ChrisLuka
Participant

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.

0 Upvotes
JenNS1
Contributor

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

SOLVE

Hello HubSpot, can you please buy this plugin? 

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

0 Upvotes
Mike_Eastwood
Solution
Key Advisor | Gold Partner
Key Advisor | Gold Partner

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

SOLVE

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

 

 

GVerma
Participant | Diamond Partner
Participant | Diamond Partner

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

SOLVE

@Mike_Eastwood how can I compare the two date property eg: Expiry date with Todays date? Can you  demonstrate the example of that workflow? 

0 Upvotes
dvel
Participant

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

SOLVE

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.

AngelaIke
Contributor

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.

sharonlicari
Community Manager
Community Manager

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

SOLVE

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


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




0 Upvotes
PamCotton
Community Manager
Community Manager

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

SOLVE

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