Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
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
Comments
-
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"
-
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?
-
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,
-
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!!!
-
I'm glad you were able to get it to a workable solution
