Skip to main content

I am looking to automate the generation of a future date for a Business Review that is based on logic of {1st/2nd/3rd/4th Day} of every {x months}.

This is similar to the Recurrence Pattern found in Outlook calendaring.

 

Has anyone found a way to do this?

Hi ​@shaydillon 

I hope a more experienced Admin can chime in and suggest something that doesn’t require using a lot of transforms, but I can think of a workaround. Perhaps not elegant, but quick to implement 😊

You could get a list in Sheets/Excel with all days for the next 20/50/100 years to be covered and another column using the EDate formula to calculate the next occurrence/s. Save as CSV in S3, and then you just merge your source date (when the last EBR happened) with the first column to get the next occurrence.
You could also create a custom calendar object using this same approach and add as many columns as you want, either assisting yourself with an external tool like in this case, and updating the object via Rules Engine, or using the advanced formula field to get values like the 1st day of the quarter for example. 

I created another column using the yyyy-MM-dd format out of habit, I’m not in the US so I use it a lot to avoid confusion 😅. The dates on the first column are random, just to demo how the formula works with a wide range of dates.
 



Hope this helps!


Thank you for your feedback.  I’ll explore whether there is an excel formula that would support the “xth/xst” “day of week” of every “y month” format.  That may make the above something we could work from.


Reply