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

[JeremyHong]

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): 

  

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?

[Ina]

We are struggling a lot with calculated properties which give no option to convert days to weeks, months, or years in the reports. Time between dates always appears as days in the reports , while in the property view it DOES CONVERT the values in years, months or weeks. That's the silliest process as it block us from creating a calculated property which would convert days to months (1 day*0,33m), or weeks etc. Hubspot leaves us with no options at all and blocks the calculated fields and reports which stops us from using it in general. It's a pity because we could really enjoy those paid features if we had more freedom..

[rwong]

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]

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]

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]

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]

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]

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.

[Neidac21]

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]

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]

@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]

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

[spiderman]

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.

[Christian_Jorge]

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]

It looks like there's a similar idea posted in another thread -- be sure to upvote that post!

[impulsekring]

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

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

[aneng]

+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]

Thank you spiderman! 

The calculation+ workflow worked great! 👍 😊 

This

[HEllis1]

I want to be able to send marking emails to contacts who have been with us for say 2 years, however the current calculation only goes up to days, I need months and years for the comms to be accuarte. 

I have created the today's date workflow and property but the calculation is letting me down. 

Any suggestions? 

[AGarcia24]

@spiderman how did you convert the field into an integer? We have an integration (pandadocs) that is rejecting the field because of the added word "day/days" and we need this number to just be a number.  

[AGarcia24]

@AFreemerman hi how did you get "days after launch 3" to show as just a number? We're trying to set up an integration and it's rejecting this field because it needs to be just a number/integer (aka can't say day/days etc). thanks