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!

@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.

Hi @KJoy3, Yes, you should be able to do this with a calculation property: Create calculation and rollup 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: Create calculation and rollup properties 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!

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)

@KJoy3 nice :slightly_smiling_face: 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.

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

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.

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.

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

Smart CRM & Sales Hub CRM

KJoy3 July 24, 2024, 6:05pm 7

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

Topic		Replies	Views
Calculating multiple if functions in a property APIs & Integrations object	2	2646	January 17, 2024
If/Then equation in calculation property CRM enterprise , admin , contacts , how-to	5	7902	August 1, 2023
Robust Formula Field HubSpot Ideas workflows	3	1516	May 22, 2024
Allow calculated properties to use date functions HubSpot Ideas object	3	1331	November 1, 2022
Ya puedes calcular el tiempo entre dos propiedades de fecha :) CRM crm	3	3479	June 11, 2025

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

Related topics