Auto-schedule child entities under a parent and ajust with weekends, holidays and vacations

Hey everyone!

I need to create a production development calendar for my videogame company along with a hiring calendar to send to some publishers that are interested in my upcoming game.

I have this Workspace which represent containers of work to do, which can be of different level of quality (quality versions like : pre-alpha, alpha, beta and gold) :

Here is what it looks like on a timeline for the pre-alpha version of a videogame level :

Right now, if I move a version’s entity, the child work’s entities move accordingly under it with the use of this formula which return a DateRange :

I use a field called [# Days] for the length of the work and another [Offset #Days] to offset the beginning of the work accordingly to the start of the version (parent entity of Work) or an other work that is dependent of to start.

What I would need is to take into account only working days (no weekends, holidays or vacations). It could just lengthen de duration of a work on the timeline if it is schedule to overlaps a not working day.

Does someone had the same needs and found a good solution?

Many thanks as always!

LF

It is possible to calculate the weekdays within a date range using formulas like the ones here:

and I can imagine that it would be possible to create formulas that would do the reverse, namely calculate a range based on including a certain number of weekdays.

However, I think it would be very tricky to calculate a range that takes into account vacations and public holdiays, since I imagine that this would require an iterative calculation.

For example, if a task is to start on Monday the 16th January, it’s no problem to work out that 15 weekdays would require 19 days in total (to accommodate 2 weekends) taking the task to Friday the 3rd Feb.
However, imagine if there are vacation days on the 20th Jan and 6th-8th Feb. It’s pretty hard to work out the end date in one simple formula. The 20th is in the middle of the initial calculated range, implying that the task needs to be pushed one weekday later. But this takes the task to Monday the 6th Feb, which means it actually needs to finish on the 9th Feb.

This results in recalculation and is likely to trigger loop detection and cause the formula to be disabled.

If anyone in the community has any bright ideas for how to address this, I look forward to hearing them