Subtask dates populate from parent

James Raikes

Hi,

Does any one know if there is a way to auto up date a subtasks dates when you move the tasks between parent tasks?

The scenario is we have a course booking tracker and the PARENT task is the course instance and the SUBTASKS are the attendees. If we need to change the attends course slot we would like to drag to a different session and the dates auto update.

The second part of the challenge is different course have different lengths and I am using a vloookup table to get other course data but cant work out how to auto change the end date based on a looked up duration?

Jeff Reisman

@James Raikes

Alright this was cool to figure out...

On the parent row for each course, use the following formulas (adjust column names to suit.)

List your start date in a Start column.

In a Duration column, pull in your course duration in days. I am assuming these are weekday courses, with holidays excluded?

For the End column:

=WORKDAY(Start@row, Duration@row, {Holidays List})

My formula includes a list of holidays I have in a helper sheet. The end date will give you the date that is that duration's length after start date in workdays.

On your child rows:

In the Start date column, use this to reference the parent of this cell:

=PARENT()

In the End date column:

=WORKDAY(PARENT(Start@row), PARENT(Duration@row), {Holidays List})

Now, when you drag a child row underneath a parent row, the formula cells for that row will show an error at first. Hit the indent button the set it as a child of the other parent row, the start and end dates will update to match the parent.

Here's my test sheet:

When I move Kacie from Course 2 to Course 3, it throws an error:

But after I indent the row, it updates with new start and end dates that match Course 3: