Creating a Function Using an If/Then Statement and Multiple Properties

[KJoy3]

Hi All!

I am look to create a custom function on a date property that mimics a formula I have used in Excel. The Excel formula is IF(cell1="Annual",(cell2+365),(cell2+90)). Essentially, I have a "Frequency" property whose dropdown includes "Annual", "Quarterly", etc. I want to create a function on a date property that reads IF "Frequency" = Annual then "Next Update Date" = Last Investor Update + 365. The function would also have to include an if/then for Quarterly frequency, etc. Is there a way to create this on a single property? Any other methods of approaching this would be greatly appreciated. Thank you!

[Abrabomer]

I’ve run into something similar before. In Excel, I would use an IF statement to adjust dates based on a condition, and it looks like you’re trying to do something very similar with the “Frequency” property. In your case, you could try using a custom formula in your tool (assuming you're working with a platform like Airtable or Monday.com) to check the value of “Frequency” and then adjust the “Next Update Date” accordingly. For example, you could use something like IF(Frequency = "Annual", DateAdd(Last_Investor_Update, 365, "days"), DateAdd(Last_Investor_Update, 90, "days")). It might need some tweaks, but it should get the job done.

[Abrabomer]

In your case, you could try using a custom formula in your tool (assuming you're working with a platform like Airtable or Monday.com) to check the value of “Frequency” and then adjust the “Next Update Date” accordingly. For example, you could use something like IF(Frequency = ""Annual"", DateAdd(Last_Investor_Update, 365, ""days""), DateAdd(Last_Investor_Update, 90, ""days"")). It might need some tweaks, but it should get the job done.By the way, if you ever want to dive deeper into formula auditing (like I did when learning Excel), I found this Sheetcast guide super helpful. It helped me better understand how to apply formulas more effectively.

[karstenkoehler]

Hi @KJoy3,

Yes, you should be able to do this with a calculation property: https://knowledge.hubspot.com/properties/create-calculation-properties (This requires a Professional or Enterprise subscription.)

The syntax is Excel-like and you get if functions and common operators to build your formula: https://knowledge.hubspot.com/properties/create-calculation-properties#functions

Keep in mind that HubSpot expects added durations in milliseconds, not days, e.g. 365 days would be 31536000000.

Without a Professional subscription this is not possible.

Let me know if you have any follow-up questions!

[KJoy3]

Thank Karsten! I managed to get the formula working for each frquency selection individually but I am unsure how to link them together into one long formula. Here are the formulas seperately. Any advice on what operators to use to combine them into one formula? Much appreciated!

if([properties.distribution_frequency]= "Annual",[properties.last_investor_update]+31536000000)

if([properties.distribution_frequency]= "Quarterly", [properties.last_investor_update]+7776000000)

if([properties.distribution_frequency]= "Bi-Annual", [properties.last_investor_update]+15,552,000,000)

[karstenkoehler]

@KJoy3 nice 🙂 you should be able to simply join these with a + sign.

if([properties.distribution_frequency]= "Annual", properties.last_investor_update]+31536000000)+if([properties.distribution_frequency]= "Quarterly", [properties.last_investor_update]+7776000000)+if([properties.distribution_frequency]= "Bi-Annual", [properties.last_investor_update]+15,552,000,000)

Give it a try and let me know if that works.

[KJoy3]

Hi Karsten! While the formula doesn't show any errors, testing doesn't yield any dates. Any suggestions? 

[karstenkoehler · Accepted solution]

@KJoy3 could you try this?

if([properties.distribution_frequency]= "Annual", [properties.last_investor_update]+31536000000,0)+if([properties.distribution_frequency]= "Quarterly", [properties.last_investor_update]+7776000000,0)+if([properties.distribution_frequency]="Bi-Annual", [properties.last_investor_update]+15552000000,0)

The if statement is missing the specification what should be returned when it's not true, adding a 0 for that scenario should work.

[KJoy3]

That did the trick!! Thank you so much Karsten! I really appreciate you workshopping this with me.