HubSpot Ideas

TH7

Include Dates in Min / Max Calculation Fields

Would like to be able to roll up minimum or maximum dates from child to parent.

 

Would also like to flag the record as being included in the calculated field so they can be enrolled in workflows.

 

For example:

Which contact has the Minimum (first) Became an MQL date? Copy that first MQL contact's source type to the company.

 

 

EDIT: Workaround similar to what @kaious posted but with timestamp offsets.

 

Would still like this functionality, but wanted to share a (cumbersome) workaround we are using with fellow users. We have Marketing and Sales Enterprise.

 

Step 1: Create a helper calculation property on the child object. Insert your date property and format it as a number. The number returned is in UNIX.

 

  • UNIX timestamps represent the number of seconds (or milliseconds, in this case) that have elapsed since the Unix Epoch, which is January 1, 1970, at 00:00:00 UTC. It is independent of any specific timezone.

 

Helper Recent Sales Email Open DateHelper Recent Sales Email Open Date

Step 2: Create a helper rollup property on the parent object. This will be set to max or min. Select your helper property from the child object. The number returned is still in UNIX.

 

Parent Object Helper PropertyParent Object Helper Property

Step 3: Create a calculated property on the parent object. Subtract the appropriate amount of milliseconds from your rollup property (for your timezone) and format as a date.

 

  • 1 hour = 60 minutes * 60 seconds * 1000 milliseconds = 3,600,000 milliseconds

Step three calculated date.png

This is not a perfect solution. I am in a region of the United States that uses daylight savings time. Therefore, time away from UNIX can vary throught out the year. Also, I had to create 3 calculated fields to get this estimated answer.

 

Ultimately, I am just trying to get the right day even if the exact time is off. This workaround will handle situations where the timestamp is closer to midnight.

 

BONUS:

I have used a larger offset when calculating quarters from dates. This is because reps often update records into the night to make EOQ deadlines. I don't need the exact datetime, to get the right YYYY-Q#. We create custom properties like this since we do a lot of analyses in spreadsheets. Exporting our date groupings reduces effort.

 

Quarter calculationQuarter calculation

8 Replies
mslarson
Participant | Platinum Partner

I absolutely agree this is needed. Having this functionality would make HubSpot's data automation much more powerful, especially when rolling up data from the contact to the company. It would be very impactful for a lot of HubSpot users to be able to see the milestone contact lifecycle stage dates rolled up to the associated company. Then you could have a high-level view of how that company as a whole has moved through your lead process.

RPepin
Member

I didn't realize this wasn't possible until now. I need a way to pull the min date from an associated deal on the contact. I've also had to build out a workflow for the exact use case that mslarson pointed out above. It would have been helpful to be able to gauge the lifecycle stage milestones of the contacts on the company level.

Ruurd
Contributor

Would be amazing to have the date of the first and last deal on the contact, using a custom date property on the deal, without any coding. Our deals are generated externally and then pushed into HS over API. Currently we have a workflow with custom code to get the date of the last Deal, comparing all Deals on the custom date property 'Date Submitted'. I would like to do the same with other custom date properties on the Deal and additionally have the date of the first deal by these custom date properties on the contact. But each time I want to have an extra date,  I need a developer to adjust the custom coding for me. Since min and max are there, it would be great to have it return dates as well. Thnx!

RPepin9
Participant

I assumed this was basic functionality that was already available until I went to create said property and realized it's not. This would be very useful in reducing the amount of workflows necessary and avoiding issues.

kaious
Top Contributor

Please please please! This would be super helpful!

kaious
Top Contributor

For anyone trying to solve for this that has a few calculation fields spare in their allowance, there is a not-too-annoying work around:

1) create a calculation field of type unformatted number on the object that has the date field you want to calculate from, then make this field equal the date field - that will result in the field being the UNIX ms value of the date field

2) create a calculation field on the associated object where you want to display the min (or whatever date). Make this field a min (or whatever) calculation based on the field created in step 1 – this will mean the field displays the lowest/highest/whatever UNIX timestamp
3) create another field on the associated object that is a date format, make this field equal to the field in step 2 - this will mean the UNIX timestamp is converted to a HubSpot date field.

 


Screenshot 2024-06-19 at 5.48.39 PM.pngScreenshot 2024-06-19 at 5.51.06 PM.pngScreenshot 2024-06-19 at 5.54.02 PM.png

Steiger
Participant

I would very much like to see this and am surprised it has less votes than I would Expect. 
Our use cases result from the fact that we have user activity from our proprietary application automatically updating via API. 
I have many dates tracked regarding the last date that user X did thing Y. 
If the max date of all the contacts using a certain feature is long ago, thats in indicator that an action should be taken or possibly that our customer (the company) is in jeopardy. 
Also I simply want to know how long a customer has been a customer related to a min date on either contacts or custom objects where we store instances of customer tenants. Thank you!

CHansen7
Contributor | Diamond Partner

Yes! Yes! Yes!

 

This would be extremely helpful. We can use numbers, so please let us grab the minimum/maximum dates on the associated records. This could greatly help with the most recent or first dates calculations or reports.