HubSpot Ideas

JeremyHong

"Time Between" calculated properties: provide exact number of days or allow us to choose D/M/Y

For calculated properties that calculate Time Between Dates, it would be awesome if we could select the output metric (days/months/years) based on how precise we need the property to be. 

 

Currently, it appears HubSpot will display the exact number of days ONLY if the two dates being used in the calculated property are within the same month. Anything outside of the month is calculated ONLY in # of months, so it doesn't display the number of months + days. With years, it only displays years, and not months or days.

 

The ability to calculate time between dates loses a lot of its value when the measurement is so imprecise. (Ie. it shows 1 year, but really there's 1 year, 3 months, and X days)

 

Here's an example:

 

Background:

  • We have created a "Today's date" deal property with a few workflows that keep it up to date
  • We have created a calculated deal property for "Days to expiry", which calculates "Time between" the Contract End Date and Today's date properties.

The "Today's date" property is not shown, but these were taken today (8/7/20): 

Screen Shot 2020-08-07 at 4.03.42 PM.png Screen Shot 2020-08-07 at 4.04.04 PM.png Screen Shot 2020-08-07 at 4.04.32 PM.png

 

Would it be possible to either:

  1. standardize the property so that it ONLY shows the number of days?
  2. have the property display number of years + months + days?
15 Replies
rwong
HubSpot Employee

Upvoting this on behalf of a customer! So far, it's only possible to show the exact value for "time between" values over a year using custom reports. Even so, the exact value is shown in days.

 

It would be useful if there's an option when setting up the calculated property to show the values in a certain unit as shared by @JeremyHong.

 

E.g. mixed = "1 year and 5 months", OR all standardardised = "17 months"

peterthegrant
Participant

This seems like an essential customization.

Our customer onboarding is very critical and happens within the first 3 months. I need to know the specific amount of days since deal closed. 2 months when the day count is 39 is very poor fidelity. 

 

Please fix this up ASAP. Even a default to day count would be ideal 

AlexVerrills
Member

This is a critical function for calculations with any fidelity. As others have said rounding to months is only acceptable for timespands of years! We need to be able to at least access this more granular information!

AllenHelms
Contributor | Diamond Partner

As indicated on several other similar Ideas like:

 

https://community.hubspot.com/t5/HubSpot-Ideas/Calculate-days-between-dates-for-reports/idi-p/242785

https://community.hubspot.com/t5/HubSpot-Ideas/Time-in-Deal-Stage/idi-p/277082

 

It's a "have-to-have" feature to allow for the tracking and "ageing" of records in the CRM and to let us have some control over this:

 

How long has a Contact been in a certain "Lead Status" without followup?

How long has a Deal been in a certain "Deal Stage" without moving?

 

This is how sales people manage their pipeline to expose "stuck" leads and deals. Gotta have this if we want to move some clients from using Salesforce where "Age" is a standard field. It's Sales reporting 101.

 

Allen Helms

HubSpot/Salesforce CRM Consultant

www.organicendeavors.com

 

 

BrettloweAUS
Member

On a related note, it would be good if the calculation could be a bit more intelligent. The time between 2 dates +/- another field is already represented as a date. 

Use case I wanted to capture the time between a ticket open and close but want to minus the number of day the ticket was "on hold" 

Is there a workaround for this?

spiderman
Participant

Here's what we have done:

  1. Created a property that updates every day for a select subset of our database.
  2. created calculation property to calculate time between two dates (the property in (1) that updates every day and a fixed date property - i.e. when they became members). 
    As you know, this displays a rounded value in days, months or years and workflow can access it in minutes, hours or days. 
    The property is actually stored in milliseconds.
  3. created a second calculation property to convert (2) to a number of years by dividing (2) by 31,536,000,000  (i.e. 365*24*60*60*1000)
    Accuracy appears to be around 0.0027 years, i.e. 1 day.

From there you can convert to an integer or otherwise use as required.

spiderman_0-1624411093329.png

 

Neidac21
Member

It would be nice to see the amount of time grouped in months or years, a little difficult to interpret a report that tells me that a client has 4,500 days in the CRM

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/

 

BrettloweAUS
Member

@spiderman 

  1. Created a property that updates every day for a select subset of our database.

    How are you doing this? The only way I can think to do this is a workflow that increases the property value by 1 and uses a time delay for each day. This sounds annoying to build. 

    Thanks
YBarOr
Participant

It's very simple to create a today property, make sure to set it to renroll of course:

YBarOr_0-1625736319229.png

 

spiderman
Participant

YBarOr That's a company workflow. It wouldn't re-enrol automatically when we tried such on a contact record. 

 

We managed to get it working on a contact record by toggling between 2 workflows every day but then the contact's activity was cluttered with workflow enrolments. We either needed a set of silent workflows that would run in the background or to write one that would run for a large number of days and then toggle. HubSpot hasn't got the capacity yet for background workflows so...

 

We chose the latter. And yes, BrettloweAUS, it was very annoying to build (especially with no ability to do loops) but it is working across several hundred contacts (our primary clients) and we can now use the date for miriad of comparisons to the current date. I have calculated data updates occuring in the background also.

spiderman_0-1625796471722.png

 

Christian_Jorge
Participant

I would like it even down to seconds, minutes and hours... we need to calculate the time from an inbound lead coming into to the first call/email from sales. Counting in days doesn't work for that usecase. 

impulsekring
Contributor | Diamond Partner

Agreed! This report looks downright silly broken down into days:

report showing time between in "days" rather than months/years.report showing time between in "days" rather than months/years.

 

 

aneng
Member

+1 Upvoting for a customer as I do think being able to have the choice to decide on the units would be super helpful! 

AFreemerman
Member

Thank you spiderman! 

The calculation+ workflow worked great! 👍 😊 

This Screen Shot 2022-05-27 at 2.36.19 PM.pngScreen Shot 2022-05-27 at 2.51.47 PM.pngScreen Shot 2022-05-27 at 2.51.23 PM.png