topic Re: Need to calculate months since beginning of the year in Tips, Tricks & Best Practices

Need to calculate months since beginning of the year

Kevin_Jorgensen — Wed, 22 Mar 2023 16:46:16 GMT

For our sales reporting, we need to know the number of months that have transpired since the beginning of the year, such that the number increments by 1 at the beginning of the next month. So in Jan, the value = 1 (special case) and stays 1 through Feb 28. Then on March 1 the value goes to 2, and on April 1 the value goes to 3 etc. I know how to calculate the number of days since the beginning of the year but only using the arithmetic functions in HubSpot's calculation feature, I can't figure out how to build a "months" function that works like the above example. Anyone ever done this or have an idea?

Re: Need to calculate months since beginning of the year

jolle — Wed, 22 Mar 2023 21:14:08 GMT

Hey @Kevin_Jorgensen, thanks for reaching out! This is an interesting use case.

It's not super fancy, but I'm wondering if you could create a custom number property and a workflow centered on the date 1/1/23. You would then add the following steps:

Set property "# of Months Elapsed" to "1" on 1/1/23
Set property "# of Months Elapsed" to "2" 59 days after 1/1/23
Set property "# of Months Elapsed" to "3" 91 days after 1/1/23
And so on (definitely double-check my math there haha)

You could then ensure that all applicable contacts/deals are enrolled. I believe that records will skip to the next applicable workflow step, so a contact created in May should pick up with 5 at the conclusion of the month.

Alternatively, you could also consider calculating the days elapsed and then divide that by 30.437 (or whatever average value works best for you) to roughly calculate the number of months elapsed. I don't believe you'd be able to round this to the nearest whole number though.

Are you able to tell us a little bit more info about what you're trying to accomplish with this property? Happy to share additional thoughts with more insight into the use case! Thanks!

Re: Need to calculate months since beginning of the year

Kevin_Jorgensen — Wed, 22 Mar 2023 21:25:38 GMT

Hi. Thanks for the suggestion. The purpose of this number is that it is
used in the calculation of the reps progress to goal. We want to use the
number of months elapsed -1 because we have revenue #s coming from our ERP
and they are the billed revenue which is always a month behind the present
month due to payment terms. I can make the calculation in XL or google
sheets easily... so I was thinking of using zapier and connecting that to
the contact record... but the workflow idea may be simpler even if it's
less elegant.

Re: Need to calculate months since beginning of the year

Thrive — Wed, 24 Jul 2024 00:38:22 GMT

Hi there, we have built an app for the marketplace that allows you to automatically calculate the time between 2 dates. Feel free to check it out here: https://ecosystem.hubspot.com/marketplace/apps/marketing/data-management/calculations-3449553