Average time between two fields

[SGarneau]

Hi !

I want to create a report that will tell me what's the average time between a deal is closed won and activates.

For now, I'm tracking the dates through 2 custom fields I created (date-picker). One is Closed Won Date and the other is Activation Date. But I can't seem to find a way to associate them and have an average come out of this.

There might be other ways to track this data ?

I'm open to suggestions on how to create that type of report !

Thank you in advance,

[elizheleva]

Hi, @SGarneau ,

From the surface, it does look like everything has been set up correctly. I would be interested, though, in finding out how the custom properties are being updated. Is it possible that the formatting of those properties are causing a glitch in the calculation?

It will be beneficial if you could share a screenshot from a specific deal (of course, removing any sensitive information) showing the values for the custom properties, based on which you have the calculation. Also, further context on how those are updated can be valuable, too. Is it via a workflow or in another way?

Thanks,

Eli

[deepikaverma]

Hi @SGarneau ,

This would be a temporary bug since I tried it and it is giving me the values in numbers only not in dates.

Thanks!

[karstenkoehler · Accepted solution]

Hi @SGarneau,

This is best done through a calculation property (option: time between), see here: https://knowledge.hubspot.com/crm-setup/create-calculation-properties

Once this property is created, you can display it in reports, for example with the KPI chart type.

Best regards!

[SGarneau]

Hi @karstenkoehler ,

Thank you for your fast reply. I just was able to create a field Average Activation with the calculation property, thanks ! However the data populated is a date that doesn't make sense. How can I visualize a number of days ?

Thanks !

[karstenkoehler]

Hi @SGarneau,

Can you elaborate what doesn't make sense? I'm not sure I'm following. The more details you provide, the better I can help.

[SGarneau]

Hi @karstenkoehler 

Let's say Field 1 is Closed Won Date [date picker] and Field 2 is Activation Date [date picker]

Now I have my [time in between] field 3 that calculates time between Field 1 and Field 2, however the data showing up is a date, but I'd need it to be a round number.

(i.e: Field 1 = 10/10/2023 and Field 2 = 11/10/2023, I'm expecting Field 3 to = 1 day instead of 01/01/1970 - which does not make sense)

Thanks !

[karstenkoehler]

Hi @SGarneau,

In your calculation property settings, did you choose the "Time between" option from the drop-down?

[SGarneau]

@karstenkoehler yes !

[karstenkoehler]

Hi @SGarneau,

It looks like you set everything up correctly. This might be a bug, it's hard to say without access to your portal I'd recommend checking with HubSpot support.