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!
Yes, you should be able to do this with a calculation property: Create calculation and rollup properties (This requires a Professional or Enterprise subscription.)
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!
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.
you should be able to simply join these with a + sign.