How can I export calculated properties as days instead of hours in HubSpot?

[LMcDonough]

Hi- I am looking for an export solution.

When using cumulative time in each stage, the export comes out with hours instead of days, and it is not helpful. Is there a way to export the data as it is shown in the  report on the screen?

[DilshadShaikh · Accepted solution]

Greetings @LMcDonough,

Based on the documentation Cumulative Time property information get stored in Time as the property name is self explanatory.

Cumulative time in [stage ID]: the cumulative time spent by the deal in the stage. This can be useful if deals re-enter a stage multiple times and you want to track their total time in the stage. When a deal is currently in a stage, it will have no value for this property, unless it had previously been in that stage. In that case, its value will include the previous time in the stage and will not update until the deal leaves the stage.

Source of the documentation here: https://knowledge.hubspot.com/properties/hubspots-default-deal-properties

This is why your export shows hours, even though the report visual in the UI may display data in a more human-friendly format, such as days or weeks. But when exporting  the data will typically be exported in its raw format.

Possibly solution would be in that scenario:

1. Use Excel or Google sheet after export, Add another column right to the Cumulative Time Property, use a calculation to convert hours or seconds to days. Example assuming if your data in Column A,

=INT(LEFT(A2,FIND(":",A2)-1)/24) & " days " & MOD(LEFT(A2,FIND(":",A2)-1),24) & " hours " & MID(A2,FIND(":",A2)+1,2) & " minutes"

2. You could create calculated properties in HubSpot that are formatted in days.

Note: This may depends on your HubSpot subscriptions.

This documentation can help: https://knowledge.hubspot.com/properties/stage-calculated-properties

Hope this helps, if it does then help the community by marking it as a solution.

Happy HubSpotting.

Thanks & Regards,
Dilshad Shaikh

[h-recker]

Great suggestions here. Best bet - a calculated property formatted in days if you have access to that in your subscription or alternative - a simple formula in your spreadsheet.

If you're finding yourself exporting this data often, you could use Coefficient's 2-way sync between HubSpot and Sheets or Excel. It's certified on the app marketplace.

When pulling this calculated property in (along with any other properties you need), you can keep the export refreshed on a schedule and check to have formulas fill down. Then, you'll place the formula just to the right of your imported table, and the formula will fill down every time there is a new row of data that comes in. 

I do work at Coefficient, so happy to answer any questions if you have any about how this would work! We have a free trial and a completely free plan.

[BérangèreL]

Hi @h-recker, thank you so much for sharing this app with the HubSpot Community!
 

I checked out the "Google Sheets Connector / Excel" app you mentioned, and I’m curious, which feature would help @LMcDonough export properties as days instead of hours?
Is it the "Formula Based Imports Native to Google Sheets?" If so, could you walk us through the steps @LMcDonough would need to follow?

If you have any examples of a calculated property formatted in days or a sample formula to use in the spreadsheet, that would be fantastic!
 

Thanks again for your help, and have a wonderful day!
Bérangère

[karstenkoehler · Accepted solution]

Hi @LMcDonough,

Out of the box, no. There is no setting that would display the values in the export file as it is shown in HubSpot.

This could however be fixed with either cell formatting in Excel or a formula.

https://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-...

A formula could look like this:

=IF(A1<1/24, 
    TEXT(A1*1440,"0.0") & " minutes",
    IF(A1<1,
        TEXT(A1*24,"0.0") & " hours",
        IF(A1<30,
            TEXT(A1,"0.0") & " days",
            TEXT(A1/30,"0.0") & " months"
        )
    )
)

Best regards

[DilshadShaikh · Accepted solution]

Greetings @LMcDonough,

Based on the documentation Cumulative Time property information get stored in Time as the property name is self explanatory.

Cumulative time in [stage ID]: the cumulative time spent by the deal in the stage. This can be useful if deals re-enter a stage multiple times and you want to track their total time in the stage. When a deal is currently in a stage, it will have no value for this property, unless it had previously been in that stage. In that case, its value will include the previous time in the stage and will not update until the deal leaves the stage.

Source of the documentation here: https://knowledge.hubspot.com/properties/hubspots-default-deal-properties

This is why your export shows hours, even though the report visual in the UI may display data in a more human-friendly format, such as days or weeks. But when exporting  the data will typically be exported in its raw format.

Possibly solution would be in that scenario:

1. Use Excel or Google sheet after export, Add another column right to the Cumulative Time Property, use a calculation to convert hours or seconds to days. Example assuming if your data in Column A,

=INT(LEFT(A2,FIND(":",A2)-1)/24) & " days " & MOD(LEFT(A2,FIND(":",A2)-1),24) & " hours " & MID(A2,FIND(":",A2)+1,2) & " minutes"

2. You could create calculated properties in HubSpot that are formatted in days.

Note: This may depends on your HubSpot subscriptions.

This documentation can help: https://knowledge.hubspot.com/properties/stage-calculated-properties

Hope this helps, if it does then help the community by marking it as a solution.

Happy HubSpotting.

Thanks & Regards,
Dilshad Shaikh