Creating a cell link to a different sheet with different working days

Dear community,

I have created a sheet for Data Center migration planning. By definition these activities are planned during normal working days (Mon-Fri) but the migrations happen during weekends, as it requires to stop servers and so that's the lowest impact to services.

This sheet has defined all days as working days to be able to use Sat and Sun as working dates.

I would like to link the cells of this sheet to another sheet (Target) that contains also links to other sheets.

Because the target has defined the working days as Mon-Fri, when I link the cells the End date is calculate to a wrong date (because it takes the start + duration from a sheet that is defined into 7 working days instead of 5).

What do you suggest to solve this issue?

- I cannot change the working days defined per sheet

- I tried but couldn't cell link only Start and End dates, without duration.

Thanks in advance,

Daniel

Find more posts tagged with

Construction

Sheets

API and Developers

Calendar View

Formulas

Reports

IT & Operations

Gantt View

Update Requests

Cell linking

Account and User Management

Comments

Brent Nathan

Perhaps you could try using elapsed durations, i.e. instead of "5d" you would enter "e5d" which makes it a flat amount of days elapsed and ignores any defined "work days"

DRodellar

For the tasks in the original sheet (7 days working days) if I replace the duration as elapsed duration for the tasks, the summary rows are still in duration (not elapsed), and these are the rows I am linking.

Is there a formula to obtain "elapsed duration" from duration?

Brent Nathan

The workaround I use for rollup sheets is to not enable dependancies in the rollup sheet. Just set your start date/end date columns to populate Gantt charts. Then you can freely cell-link dates no matter what the source is using because it doesn't require the duration fields at all,

DRodellar

The workaround to have an intermediate sheet where dependencies are disabled seems to work.

Using the formula with workday gives me a much closer data =NETWORKDAY([Start Date]3, [End Date]3) + 1, even though it is never going to be exact by definition (as what is ending in a weekend has to end on Friday or next Monday when considering Mon-Fri).

I need to create this 3rd sheet because I want to keep my other tasks and dependencies in the target sheet.

Thanks Brent! That works for me!!!