I’ve been looking at the issue of manipulating Hubspot date/time properties a lot of the community discussions around the topic. After some experimentation I’ve come to the conclusion that datetime based property calculation is possible using a little brain surgery – and within some limits – and that proper support for such functionality seems very possible – perhaps even an imminent upgrade.
It’s worth noting that others have working on filling this gap with some clever app integrations – such as https://app.hubspot.com/ecosystem/6893619/marketplace/apps/marketing/analytics-data/integration-agen... which underscores the true demand.
My project began when I needed to calculate the end date of a contract, represented by a Hubspot Deal object, based on the start date (potentially different from the deal close date) and a contract term in months. I’ve no doubt, based on the community discussions, that similar business logic & workflow challenges exist for Contacts and other Hubspot object types that can be addressed by the approach described below.
The building blocks for calculating dates are as follows:
- The calculation property type.
https://knowledge.hubspot.com/crm-setup/create-calculation-properties
At the time of writing, the documentation on this was dated 23rd Jul 2020, but it’s not clear when the current functionality was added.
- The API endpoints that permit a PUT and PATCH operation to a Hubspot object type. https://developers.hubspot.com/changelog/patch-method-for-updating-crm-object-properties
- A tool to manipulate the API like Postman
- The knowledge of the Unix epoch time approach behind time and date stamps in Hubspot
https://www.epochconverter.com/ and the date and datetime property types used to store this behind the scenes.
In order to create a calculated datetime property, its necessary to use the API to configure the property manually using a tool like Postman. The combination of property variables required seems to include a “type” of datetime and a “fieldType” of calculation_equation plus setting “calculated” to be true. Overall the data structure is likely to look like this:
Regular datetime and date values can be used by the calculation property “time between” option to arrive at a difference between two dates. The result of this is a number type property which is the number of milliseconds between the two inputs. For enquiring minds, this is a multiple of 86,400,000 representing a day in Unix millisecond time.
My simple calculation required a fixed date input (Start Date) and a number of months (Term) to arrive at the calculated End Date. I used a “time between” calculation (between Unix Epoc and Start Date) to create a number value representing the Unix version of the start date, then set up the End Date as a calculated datetime property that uses a “custom equation” with the Term number (and some large constants) to create the right value. This date can then be displayed and used as a normal datetime property elsewhere in Hubspot. My first report that used this property was one that alerted the team to contracts that expired in the next 60 days and I’m sure there will be many more.
Limitations and fragility that I’ve found to be wary of:
- dates can only be properly used if datetime values are rounded to the start of a day. Things that break if you don’t observe this limitation are filters in reports and views and even workflow copies from datetime to datetime properties. If you want accuracy in turning numbers of months into times difference in milliseconds, then the multiplier 30.417 used in a naïve way results in values that are not sufficiently constrained and will cause downstream app errors of various sorts.
- It’s still annoyingly restrictive not to be able to use a workflow copy operation between a datetime and a date object.
- To properly use a fixed time reference, such as the Unix epoch, a property much be set manually or via a workflow to the required fixed date. It does not seem possible to default a property to a zero or other fixed value in a conventional way. If you use a calculation (say =1-1) to set up a fixed datetime property value, there’s an odd limitation that prevents the fixed property being used in a “time difference” calculated property.
- There’s an arbitrary limit of 5 on the number of calculated property values that can be used (at least in the Professional platform). I can’t find any documentation that mentions this, but it may be a function of the platform tier.
- Given the unsupported nature of this brain surgery, it could become unusable at any time as a side effect of other updates or being locked down by product managers for whatever reason.
My plea after this effort is for Hubspot to build upon what clearly is a mostly working framework and make a set of date calculation functions available to the average user.