It would be geat to have a native feature that will calculate the age for contacts rather than the workaround via workflow by creating a custom property "today's date".
it's possible to use a workflow and a calculated property to approximate their age, but even that doesn't quite work, as calculated properties round to the nearest number, so if a contact is e.g. 23 years and 8 months old, it will round up to 24 years old.
After some work, here's the best solution/workaround I've been able to come up with.
To get a calculated age:
Create (if you don't have one) a date property for Date of Birth
Create a date property to store the last date age was calculated. e.g. "internal - last date age calculated"
Create a calculation property (e.g. internal - calculated age (rounded)) that is the time between "Date of Birth" and "internal - last date age calculated"
Create an active list (e.g. "Workflow - requires age update") whose rule is "if last calculated age date hasn't been updated in 30 days or is empty" AND "Date of Birth" is known. (feel free to choose a different frequency if desired. I just didn't want to bog down the system unneccessarily and once a month seemed fine)
Create a workflow that enrolls anyone in the previously made list. be sure to enable re-enrollment. this workflow will only have one step: update the "internal - last date age calculated" property to "today's date".
This setup mostly works, with one exception -- calculated properties will round times to the nearest year when displaying the value in a contact view. so if someone is e.g. 31 years old and 6+ months, then they will be rounded up to 32 years old. A workaround to get a more accurate age:
Create a calculation property (e.g. Calculated Age) and give it a custom formula of: "internal - calculated age (rounded)" / 31536000000
why 31,536,000,000? because the time between property ("internal - calculated age (rounded)") is stored in 1000ths of a second. so 1000 * 60 seconds * 60 minutes * 24 hours * 365 days = 31,536,000,000.
Afterward, your new "Calculated Age" property will display age with several decimal points. e.g. 22.7893.
Hope this helps others. I'd much prefer a built in field, but this will do for now. haha.
>1000 * 60 seconds * 60 minutes * 24 hours * 365 days = 31,536,000,000. This is not considered about a leap years. I believe the formula below is better 1000 * 60 seconds * 60 minutes * 24 hours * 365.25 days = 31,557,600,000
I followed the instructions of Arden with the correction from @YInui and just wanted to share my experience...
So one thing to note is apparently we just get 5 calculation properties. Just for this to work I had to use 2. So we're up to 4 out of 5 being used. Kind of annoying to see the little note that's like "you're using 4/5, talk to sales" when really they just should be fixing it themselves. But I digress.
So we already had a DOB property. Then I created the property "internal - last date age calculated". And I created an active contact list with these filter criteria:
Basically, it means that the ages for our activie clients will be within 2 days of correct and the ages for people in our database who aren't our active clients will be within a month of being correct. That was my compromise.
Then I created the workflow described, so once someone was added to that list, the "internal - last date age calculated" date gets updated to the date this action is happening (re-enrollment allowed).
Next I created the calculation property "internal - calculated age (rounded)". This part was a bit tricky. So you create a new contact property. Click next and field type is calculation. Then "build calculation" and in "calculated property type" you need to change it from "custom equation" to "time between". I didn't quite understand that from the above directions so I had to call Hubspot for help. Once I did that, then I was able to find DOB and "internal - last date age calculated" to make that property work.
Then I did that second calculation property described (Calculated Age) and used the 31,536 number but when I tested it, I found that the calculation was wrong. Someone who was 83.9 something was showing as over 84. So then I changed the number to the 31,557,600,000 listed by @YInui and I believe my Calculated Age is fairly accurate now!
I hope this helps. Anyone that needs this, I encourage you to one, upvote this original question and 2, call support to get help doing this work around, then they'll start to see how many people need this and one day maybe they'll fix it!
We've developed an app, PocketKnife for the HubSpot Marketplace that does the trick using Workflow actions. Check out the details here: Age Calculator App.
The app is now available in the HubSpot Marketplace. Click here to access it.
@hubspot any development on this one? A today's date field for reporting would be so useful. The workflow work around adds a workflow process to every contact everyday and clutters up the activities screen rendering it useless for monitoring other workflow actions. Our organisation is also very restrictive about using third party solutions and add ons.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
