HubSpot Ideas

jackielee

Create a property to calculate the "count of days since XYZ"

I've seen this in a few instances and it was brought again to my attention how beneficial it would be to have a contact property that calculated how many days since XYZ; for example, how many days since create date, how many days since last contacted, how many days since became an MQL, how many days since last marketing email sent. 

 

This would really help customers keep track of a contact/company/deal timeline in many different ways!

17 Replies
BrianTassey
HubSpot Employee

Agreed! I think HubSpot Users would benefit greatly from the ability to create calculated properties based on dates, rather than just other number properties. 

samglenn
Top Contributor

YESSSSSS. This would be incredibly helpful. 

jieyintan
HubSpot Employee

This feature would be extremely helpful in a lot of ways! 

For teams that base their SLA on metrics such as number of days since the deal has been created, a feature like this would reduce so much friction when it comes to building reports. 


Now, users will have to do the math themselves to calculate how many days it has been since the deal was created etc. 

cmetgy
Member

That would be helpful for us too !

mnagel
Participant | Diamond Partner

Hello,

I may have come up with a workaround for this now that HubSpot does offer the ability to calculate the time between dates with calculated properties.

 

You'll need to first create a new date property on the object you'd like to calculate the time between. That property will house today's date. Then, create a workflow to set that property if it's unknown or more than 1 day old. Make sure the workflow is set to reenroll contacts based on your criteria.

 

Screen Shot 2020-08-29 at 2.15.05 PM.png

 

Now that you know today's date (and it's constantly being updated) you can set up a new property to calculate the time between that date and any other date on the object.

 

 

jesse_webstacks
Member | Elite Partner

Howdy, @mnagel

 

Love this idea; thanks for sharing the workflow details. I just built it in my portal to test – with one difference being I've set it as a Contact property and I'm building it as a Contact enrollment trigger – however, I seem to be running into this issue for re-enrollment to update the property each day: 

Screenshot on 2020-09-26 at 15-48-30.png

Source: https://knowledge.hubspot.com/workflows/add-re-enrollment-triggers-to-a-workflow#available-re-enroll...

 

In your example, you mentioned that the "Today's Date" property would constantly be updated, so I'm wondering how that works?

 

Here's what I'm getting when I turn the workflow on:

Screenshot on 2020-09-26 at 15-54-27.png

 

Appreciate your help!

mnagel
Participant | Diamond Partner

Hi @jesse_webstacks,

I actually just ran into this roadblock as well and have been working on another workaround. For whatever reason, HubSpot doesn't share the same reenrollment ability across all objects. As you can see from my original screenshot, I was setting this up on the deals object and reenrollment based on date is possible there. However, as you point out, it's not possible on the contact.

 

To solve, I had to use a couple of workflows and a static list in addition to the already created "Today's Date" property to force reenrollment.

 

Workflow 1 enrolls anyone with an unknown "Today's date" which doesn't need reenrollment AND anyone that's a member of a static list (which you can base reenrollment on). From there, the workflow sets today's date, removes contacts from the static list (if they are on the list) and enrolls them in workflow 2.

Today's Date Setter.png

Contacts can only get into workflow 2 if they are sent from workflow 1 (make sure reenrollment is enabled in WF2) and waits until 12am (+ a 15 min delay) to add them to the static list referenced in workflow 1. This creates the loop of enrollment and timing needed to set that property on a daily basis.

 

Today's Date Setter (2).png

 

 

 

As you can see, I'm working in random delays to make sure HubSpot doesn't get too far ahead of itself before contacts had a chance to settle in a list or workflow.

 

It would be much easier if HubSpot would allow reenrollment for date properties in contact workflows or provided today's date as a property by default - until then, this has been working for me. Interested to hear if there are other solutions to this because this one isn't ideal with all the additional assets and moving parts.

rvzello
Contributor

Now that there is the ability to calculate a property as a "Time Between", can we simply add the "current date" as one of the options in the drop-downs, to complement the available date properties?

PDuque
Participant

While that idea is not approved and implmented, I would suggest using GoTellMe Calendar Toolkit https://gotellme.com/which-functions-does-calendar-toolkit-have

https://hubspot.gotellme.com/CalendarToolkit/

 

Smcpherson
Member

I am using an active list with a workflow to update my "Today's Date" field and it seems to be working okay.  Here is what I did...

 

1. Create date field called "Today's Date"

2. Create active list called "Update Today's Date"with criteria:  [Today's Date] = unknown OR [Today's Date] > 1 day ago (this pulled in all my contacts from Hubspot)

3. Create workflow with enrollment trigger:  List membership = [Update Today's Date] THEN set date stamp [Today's Date] = date of step

4. Update workflow settings to allow re-enrollment if member of [Update Today's Date] list;  run at specific time every day = 12:00 AM; when contact no longer meets enrollment criteria = remove from workflow

 

When I turned on the workflow, it pulled in all my contacts from the active list and updated the field with today's date.  My active list refreshed and 0 contacts are part of the list and 0 contacts are now enrolled in the workflow.  Presumably, tomorrow all my contacts will be picked up by the active list because it's been more than 1 day and that will trigger the workflow steps.  This process will repeat each day at midnight.

 

Now that i have a working Today's Date field, I can use the calculation field type in Hubspot to calculate time between XYZ date field and today's date giving me "Days Since..."

shajones
HubSpot Product Team

Hi all!

I'm Shay Jones, a Product Manager on the CRM product team.

 

Thanks to everyone for taking the time to submit, upvote, and comment on this Idea. We’re still reviewing this idea and trying to determine the best way to implement this in HubSpot. Turns out it’s a fairly complex engineering problem. To better understand all of the use cases for this type of field, we've created this survey and would very much appreciate it if you all could take a couple of minutes to fill it out.

 

Thanks,

Shay

dspsolves
Member

Thanks for bumping this idea into consideration, @shajones. I've filled out the survey form.

 

For more context, we maintain spreadsheets with conditional formatting that highlight the cells based on "days remaining" or "days since". The highlighting part can be ignored however the fundamental objective is to take informed decisions without having to dive into this calculation.

MBrunel-Walker
Member

this is another major limitation in HubSpot - i want to be able to create report that show for example how old a lead is, or more likely - 

 

IF LIFECYCLE = MQL and LEAD STATUS = NEW and "time since it became a lead status new" IS MORE THAN 3 days, then [WORKFLOW] set a TASK in Salesforce

 

or IS MORE THAN 5 DAYS - send an email to Sales Director

FPalmisano
Participant

It would be really great to have the AGE calculation as a value already available without workarounds

Wfilters
Member

Greetings, everyone!

Allow me to introduce myself. I'm Shay Jones, a Product Manager working with the CRM product team.

First and foremost, I would like to express my gratitude to each and every one of you for taking the time to submit your ideas, upvote suggestions, and provide valuable comments. Your contributions are immensely appreciated.

I wanted to take a moment to provide you with an update on the progress of this particular idea. Our team is actively reviewing it and carefully considering the most effective approach for implementing it within HubSpot. As it turns out, this idea poses a rather complex engineering challenge that requires thorough examination.

In order to gain a comprehensive understanding of all the possible use cases for this specific field, we have prepared a survey. Your insights and feedback are invaluable to us, and we would be extremely grateful if you could spare a couple of minutes to complete the survey.

Thank you all for your continued support and cooperation. We look forward to your participation in the survey.

cmetgy
Member
Hello, where is the link to the survey ?

Thanks
karenkaz
Participant

I filled out the survey but it's kind of frustrating that the survey didn't ask about how you might use this function to build active lists, which is our primary need. We are a membership organization and our memberships have a Paid Through Date (PTD) which is a property synced to HubSpot from our member database. Most of our emails are members-only, so it's critically important that we can build active lists that only include contacts with a current membership. But without an ability to compare today's date to the PTD, we have to manually go into those list filters and update them to include contacts with a PTD after the date we select in the date picker. 

 

Luckily, our memberships are pro-rated into quarters so we only have 4 possible paid through dates and only have to do this 4 times a year (for each subscription type...). But if we were to ever move to an anniversary-style membership cycle where each membership expires exactly 1 year after joining, we would have to update each of these lists daily which would be a huge problem. 

 

I don't even care about calculating the number of days, just the ability to check if a date property is before/after today. I tried, but you can't even create a custom calculated property that compares a date field to today's date - the only way to use a Duration calculated property type is to calculate between two existing date fields. It's similarly annoying in building reports to have to manually edit them to update a date range, which often defeats the purpose of building report dashboards for internal stakeholders if they have to know how to update the date range to get a more current report.