Ok so you installed Tick Tock Today, and you've got an absolutely legendary property that is updated with Today's Date everyday. What do you do?
Well, probably like everyone else, you immediately started created calculated fields that used the Time Between calculation option to create properties that displayed time between another date picker field and today's date. Good for you champ, look at you go.
But then, you had a thought. And it went something like this...
"Hey, I could drop this Time Between value into an email as a personalization token".
And that was a GOOD thought. And maybe you even tried it. If you did, you may have been disappointed to see that when you use a Time Between Calculated Property as a personalization token it looks like this...
Yup that's right. The value of Time Between calculated fields is stored in milliseconds. Rightfully so, its a hyper accurate unit of measurement, and since those time between fields can be used to measure things down to the second, it makes sense.
But what if you want to display that measurement of time in a string so it can be used as a personalization token, just like you see it on your CRM records.
I asked myself this same question. And when I quickly came to the conclusion of "I have no f**king idea" in no more than 10 seconds, I pulled the classic "Who wants to be a Millionaire" move and phoned a friend.
That friend, was Carter McKay. The man the myth the legend from PivIT Global.
Of course the boy genius has a formula. And the best part it, is that it does not require Operations Hub formatting actions in Workflows, it's simply just a calculated property using Advanced Mode, a new way to create custom calculated properties in HubSpot.
If you want to be able to print Time Between 2 properties as a personalization token to use in content, you can use the following formulas depending on if you want the time between to render as days, months, or years.
To get a personalization token to print a value as "X years" use this Advanced Mode calculated property.concatenate(number_to_string(round_nearest((time_between([START DATE], [END DATE]) / 31557600000), 0)), " years")
To get a personalization token to print a value as "X months" use this Custom Mode calculated property.
concatenate(number_to_string(round_nearest((time_between([START DATE], [END DATE]) / 2629800000), 0)), " months")
To get a personalization token to print a value as "X weeks" use this Custom Mode calculated property.
concatenate(number_to_string(round_nearest((time_between([START DATE], [END DATE]) / 2604800017), 0)), " weeks")
To get a personalization token to print a value as "X days" use this Custom Mode calculated property.
concatenate(number_to_string(round_nearest((time_between([START DATE], [END DATE]) / 86400000), 0)), " days")
To explain what this formula is doing, think of it this way. The Concatenate function is what is connecting the value of the number to the word "years, months, or days". The Number to String function is changing a number value to a string value. Round Nearest is rounding that number to avoid decimals. The very large numbers you see represent that corresponding unit of time in milliseconds, so it divides the total milliseconds between the dates to give you a number of years, months, or days. Finally, the Time Between function is working the same way as a normal time between property would work.
In these formulas, you want to replace the text of [START DATE] and [END DATE] with the insertion of the proper date fields. Which will look like this when you do it.
Now, when you use calculated properties that are using that Advanced Mode formula, you can use them in personalization tokens and it will print what is actually in the field vs gigantic number of milliseconds!
Huge shoutout to Carter McKay for showing us the light on how to get more out of these Time Between custom calculated fields. We know its a big use case that our customer's wanted when we were building Tick Tock Today, and now thanks to his expertise, y'all can take full advantage of the values of those properties in your personalized HubSpot content!
