Hey Everyone, Any ideas on an expression for ...

expressions
(Hyman van Zyl) #1

Hey Everyone, Any ideas on an expression for recurring dates with recurrence period determined by the users choice of number of months? Say for instance you have a Task with Start and End date and when it is completed you want it to update the dates to 2 months ahead…or if the user chose 6 then 6 months ahead etc. The weekday of the Start Date need to remain the same. Thanks Hyman

(Aleksi Alkio) #2

You could run an event action(s) when the record is completed. The formula in that action is not very simple but of course doable.

(Hyman van Zyl) #3

Thanks Aleksi, I am using a few actions and got something working after lots of trial and error. Greets

(Aleksi Alkio) #4

It would help others as well if you could show your solution, thanks.

(Hyman van Zyl) #5

Hey Aleksi, with pleasure. So I have tasks linked to a project and have an action which appear in the project as soon as all tasks are completed which changes all the task dates into the future according to how many months the user selected in a column under projects for the tasks to recur. So you dont keep a copy of the old task, the same task just gets moved into the future. The task will move to a date in the future closest to the original date but remaining on the same day of the week. The only way I could get this to work was by adding an extra task start date column which copies the start date into the initial value. So you have an initial value of the original start date which will stay the same in spite of the start date recurring (Earliest Initial Start Date).

Then you have to count the times recurring, times it with recurring moths and times it with 30.44 and add this to the MIN value of the initial start date. By using the WEEKDAY section you end up with the closest weekday to the original date.

=[Earliest Initial Start Date]+ROUND(30.44*[Total Months])+(WEEKDAY([Earliest Initial Start Date])-WEEKDAY([Earliest Initial Start Date]+ROUND(30.44*[Total Months]))) This only gives you the first date of recurrence. Subsequent tasks dates are set by adding the amount of days they differ from the first date. Tasks end date can be arranged with a task days column. The action ends up to be many action type triggering about 4 actions in projects which again are based on another 4 under tasks. I am very open for suggestions if there is an easier way. I can share the app with anyone who is interested when it is done. Bit hard to explain. Greets