Tips, Tricks & Best Practices

Kevin_Jorgensen
Participant | Gold Partner
Participant | Gold Partner

Need to calculate months since beginning of the year

SOLVE

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? 

1 Accepted solution
jolle
Solution
Recognized Expert | Partner
Recognized Expert | Partner

Need to calculate months since beginning of the year

SOLVE

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:

  1. Set property "# of Months Elapsed" to "1" on 1/1/23
  2. Set property "# of Months Elapsed" to "2" 59 days after 1/1/23
  3. Set property "# of Months Elapsed" to "3" 91 days after 1/1/23
  4. 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!

Jacob Olle

Marketing Operations Manager

HubSpot Certified Trainer

Create Your Own Free Signature

View solution in original post

3 Replies 3
Thrive
Contributor | Elite Partner
Contributor | Elite Partner

Need to calculate months since beginning of the year

SOLVE

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

0 Upvotes
jolle
Solution
Recognized Expert | Partner
Recognized Expert | Partner

Need to calculate months since beginning of the year

SOLVE

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:

  1. Set property "# of Months Elapsed" to "1" on 1/1/23
  2. Set property "# of Months Elapsed" to "2" 59 days after 1/1/23
  3. Set property "# of Months Elapsed" to "3" 91 days after 1/1/23
  4. 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!

Jacob Olle

Marketing Operations Manager

HubSpot Certified Trainer

Create Your Own Free Signature
Kevin_Jorgensen
Participant | Gold Partner
Participant | Gold Partner

Need to calculate months since beginning of the year

SOLVE
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.
0 Upvotes