Forum Discussion
Set Date in Future based on Specified Freqency
Hi! I am trying to use Lists to manage ongoing engagements and am needing some advice on how to accomplish a something. I'll list the details then my question(s):
- I have a "First Engagement Date" set for 4/1/2023 (this is a calculated column based on another date in the list)
- I have a "Frequency" setting that will either be Quarterly or Monthly
- I have a "Future Engagement Date" that is a calculated column that automatically calculates based on their "First Engagement Date" and "Frequency" settings, using this formula:
- =IF(Frequency?="Quarterly",DATE(YEAR([First]),MONTH([First])+3,DAY([First ])),IF(Frequency?="Monthly",DATE(YEAR([First]),MONTH([First])+1,DAY([First]))))
The problem I'm experiencing is that the Future Engagement Date is static, meaning that based on my First Engagement Date and the Quarterly Frequency, it is showing at 7/1/2023. But what I need it to do is to calculate only FUTURE dates based on their First Engagement and Frequency. So the result I need to see is 1/1/2024 - which is their Future Engagement Date, based on a quarterly schedule from their First Engagement date.
Is this something that can be accomplished? This may throw another kink in the system, but that Future Engagement Date will also need to be calculated based on a Status column. While the engagement is happening, the Status will be "In Progress," but once I update the Status to "Completed," I'll need that Future Engagement Date to calculate the NEXT engagement date. Any advice you have is helpful!
- I wouldn't reference calculated columns. Just redo the calculations from the original data fields. Use Excel to create and test your formula. Ex: instead of [First] you would paste the entire calculation used in [First]. Alternatively, you can create a standard date field and use Power Automate to auto populate it.
- Thank you for your help! So the reason why the First Engagement Date is a calculated column is because I have a Sale Date and the First Engagement Date will always be the 1st of the month after the sale (e.g. Sale Date is 6/18/2023, so the First Engagement will be 7/1/2023). So what you're recommending is that I instead put the formula I used for First Engagement directly into the [First] portion of the formula for Future Engagement Date? Will that resolve the issue about only showing future dates?
alexxxandria To integrate the Status into your formula you can wrap the whole thing in another IF statement based on the Completed Status. Since the formula is getting complex it's best to try it out in Excel.
IF(Status="Completed", Date1, Date2)
