Auto-Rescheduling Tasks (Based On Tasks Being Dragged)

Please see attached screenshot on just a draft option on what I am playing around with. I want to drag Task 4 to actually be Number 1 priority and for that Task to be completed 1st (however I don't want to use the Dependencies feature as the tasks are NOT linked, they are all separate) and I want the dates to all change automatically for all 4 other rows. In other words, if I drag a task to the very top of the table I want the dates to auto-populate for all other tasks based on a different task being a priority. Please tell me this can be done, can someone please explain it to me via a formula / automation.

On a separate note, I have put a formula in if a task wasn't fully completed, it would automatically detect on how many remaining hours are needed and this would push the task to the next day, and then it would push out the other tasks and so on. I don't know if that will have an effect or an impact on what I want to achieve in my first paragraph.

Would really appreciate if you could let me know if its possible, as it would be a life-saver!🤗 Hope the above makes sense!

Find more posts tagged with

Forms

Automations

Cell linking

Accepted answers

All comments

Philip Robbins

@lauracorcoran11 what you're after may be possible, but it might take some time and effort to work through the logic. Last year I was playing around with something like this and I managed to get a sheet scheduling tasks throughout a day and then flagging any that ran beyond the end of the day. These would need to have their date changed manually in my build, but they get flagged with conditional formatting so they draw your attention.

I don't think I tried driving the start dates based on the absolute priority. It could work, but as I say, it would need a bit of time and effort to look into.

Philip Robbins

Okay, I think I might have something…

Formulas as follows:

Priority: =MATCH(Ref@row , Ref:Ref, 0)
Int Days: =INT(SUMIFS([Estimated Effort (Hrs)]:[Estimated Effort (Hrs)], Priority:Priority, <=Priority@row ) / [Working Hours]#)
Start date: =IF(Priority@row = 1, Today@row , [End Date]@row - [End Date Change]@row )
End Date: =IF([Mod Hours]@row = 0, Today@row + [Int Days]@row - 1, Today@row + [Int Days]@row )
End Date Change: =IFERROR([Int Days]@row - INDEX(COLLECT([Int Days]:[Int Days], Priority:Priority, Priority@row - 1), 1), 0)
Cumulative Hours: =SUMIFS([Estimated Effort (Hrs)]:[Estimated Effort (Hrs)], Priority:Priority, <=Priority@row )
Mod Hours: =MOD(SUMIFS([Estimated Effort (Hrs)]:[Estimated Effort (Hrs)], Priority:Priority, <=Priority@row ), [Working Hours]#)

Notes:

Ref is an autonum column.
I have a sheet summary field called Working Hours (current value = 8).
Today needs to be set daily via an automation for every task.
Scheduling will be based on what needs to be done from today's date.

I haven't fully tested it, but let me know if it works for you.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.