"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?

[DHenderson81]

The simple fix to this would be when we create a "days between" calculation we should be able to specify if we need it "exact" as in the exact number of days, or if we want it to be "rounded" like we are currently seeing. 

[RyanAbilar]

This is a show stopper! Badly needed. 

[Clayton71]

Can we get an update from a HubSpot member on this request? 

[RyC]

Really, really need this.  Rounding decimal points and selecting desired duration units (days, weeks, months) on reports should absolutely be a built-in function.

I want to track average close times.  A report that tells me I had 1 deal close in 2.3 days, 1 deal close in 2.4 days, 1 in 2.5 days and so on is pretty useless.

[ETan4]

Or better yet, give users an option to choose which formatting they prefer whether it's days, months, years, or a combination of those.

[ETan4]

Workaround for now:
Create a calculated property to convert the "Time in between" property to either days or months. An important note is that the time in between property is converted to milliseconds if used in a calculation or if copied to a number property.

Formula for calculated property:
[Time in between] property / 3,600,000 = Hours
([Time in between] property / 3,600,000 ) / 24 = Days
(([Time in between] property / 3,600,000 ) / 24) / 30 = Months

[MatthieuDumont]

I absolutely need this

[bj_ndeseyssel]

Need this also ! 

[Lorenzoenns]

Very useful if this was implemented for our business. 

[FrankE]

Workaround based on the idea of @ETan4 
If you use the Formula editor when creating the property, it is possible to achieve the same without the need of creating an own [Time in between] property.  It could look something like this to get the number of days between two dates:

[DFin]

Hi. I would also like a fix for this. I would like to see my open deals in age by days. Much like how the header shows my aversage deal age in Days. I need to be able to view individual deals age in days.

[Adriane]

I totally support this idea! In our case, I've noticed that when using calculated properties with the "Time Between" type the displayed time is often rounded to general intervals like "a few seconds," "a few minutes," or "a few hours." While this provides a rough estimate, it lacks precision, especially when dealing with time-sensitive metrics. I propose an enhancement to allow for more accurate time display, including exact minutes and hours like we have with the property "Time to Close." This would provide users with a clearer understanding of the data and enable more precise analysis of time-related metrics.

@MaxFesten 

[GWebb6]

Hi
I've been following this thread and there are some great responses.  I'm after some guidance - I have a use case where we want to show the time between 2 dates in Hours, none of my calculations are quite correct and the output is not showing properly.  

So far, I create a calculated Time Between property with a Duration format (milliseconds).  The output is great and shows hours until you get to a full day then it starts displaying as 2 days, 3 days etc instead of 48, 36 hrs.

So I wonder whether I also need to add some logic to convert the days into hours 24 IF the hrs are more than 23? Or create a property that converts days to hours IF the hours are more than 23?

Any advice would be greatly appreciated 🙂

[ABeian]

@GWebb6, the automatic conversion in "worded units" (x days, months, etc.) cannot be turned off, in my experience.

When building reports, HubSpot will pull the actual number behind, but if you intend to use lists of views then you will need a separate property to make the conversion.

Below an example:

The end result:

Hope this helps!

[GWebb6]

@ABeian Yes that works - thank you so much for the example, I really appreciate it! 🙂 

I've upvoted this as it it would be great if we could have properties displayed in days, hours or even minutes 🙂 

[LW27]

I used a formula to calculate the days between two properties and covert the milliseconds into days. Hope that helps.

[LW27]

I used the Custom equation to calculate the days between two properties and convert milliseconds to days. Hope that helps. 

[GLadd]

The above solution from FrankE works!  Thanks.

[ErikaJ]

Though I can't believe that Time Between in specific measures (days, months, years, etc) is not a default property option, I used the above solution from FrankE and it worked great. I also edited to calculate by Months instead of Days with a decimal place (as we needed to show 2.5 months for example). Here's the formula as changed for that example.