Multiple working days calendar

1) One way to do this would be to have two separate sheets, each with their own Project Settings set up in Gantt view. Then you could use a Row Report to join the data from the two sheets together.

2) Another option, if you're not using Project Settings, would be to set up a Start Date column, a duration column (not linked to Project Settings), and an End Date column with a formula.

The Formula would look at the Department in that row, and then either simply + (add) the Duration days to the Start Date for "Manufacturing", or use the WORKDAY function to add the duration in working days to the Start Date for "Corporate", like so:

=IF(Department@row = "Manufacturing", [Start Date]@row + Duration@row, WORKDAY([Start Date]@row, Duration@row))

Cheers,

Genevieve

@Genevieve P. thank you for the formula, in the above screenshot "duration" is mentioned as "1" then this case the end date should be dec 12, right? but its taking 13dec as end date. how can we fix this? please let me know

same way its calculating one day additional (Ideally the end date should be 21/12/22)

In this instance, you can subtract 1 from the final output like so:

=IF(Department@row = "Manufacturing", ([Start Date]@row + Duration@row) - 1, WORKDAY([Start Date]@row, Duration@row) - 1)

Cheers,

Genevieve

If I select corporate(5 working days (Monday to Friday) its this is ending on SUNDAY, can you please help

Ah of course, my apologies you will want to subtract 1 from within the WORKDAY function, not outside of it.

=IF(Department@row = "Manufacturing", ([Start Date]@row + Duration@row) - 1, WORKDAY([Start Date]@row, Duration@row - 1))

@Genevieve P. its working, Thank you so much!

one last thing, In place manufacturing if I want to check Scientist column and if there is any value in any cell it should calculate 7 days. and in place of corporate I want to check project manager column (replacing department column with Scientist and project manager)

It sounds like you're looking to see if a cell is blank or not. In a formula, you can check this by searching for "" which indicates "blank". You can also use <> to say "does not equal".

For example:

=IF(Scientist@row <> "", ([Start Date]@row + Duration@row) - 1, WORKDAY([Start Date]@row, Duration@row - 1))

Genevieve P. thank you!, is there a way to add one more calendar for 6 days? (Monday to Saturday)

In this instance, I would actually recommend my first suggestion:

Set up multiple, individual sheets, each with their own Project Settings in Gantt view.

One sheet would have a M - Fr work week.
Another sheet would have a M-Sun work week.
And finally, a third sheet could have a M-Sat work week.

Then you could use a Row Report to join the data from the three sheets together, using the Summary function in the Report to create calculations across all three sheets. (You could also use cross-sheet formulas, if the report doesn't work for you).

Genevieve P. I can you please tell me how can we add predecessors if we have 3 sheets and also we have more than 500 projects running/year and if we create 3 sheets for each projects, it will be more than 1500 sheets, is it possible to manage? I'm worried about collating sheeting and preparing reports.

also do you have any custom project management template? help needed here

https://help.smartsheet.com/learning-track/getting-started-resource-management

You're correct that Predecessors cannot carry across multiple sheets. However if you're using Dependencies and Predecessors then you won't be able to use formulas (like above) to calculate the End Date of a task - this will be done based on the Start, Finish, and Duration columns, based on the days defined as Working Days in your Project settings. (See: Project Sheet Columns: Start Date, End Date, Duration, % Complete and Predecessors)

If you're using Dependencies and Duration, you can use elapsed time to ignore what you have set as non-working days. For example, instead of 5d in the Duration column you would put e5d. See: Work with Duration and Predecessors

If you have complex time schedules and are looking to manage resources across your projects, you may want to look into Resource Management by Smartsheet.

Cheers,

Genevieve

Thank you @Genevieve P. if I use resource management will it solve the multiple calendar problem? say for example if I define work days based on an employee profile and Department in Resource management, and I'm assigning task in a project, will this calculate based on his work days assigned in RM(example Monday to Friday) or will it consider the project settings work days(example Monday-Sunday)?

@Genevieve P. @Paul Newcome if I use e5d in duration column to ignore non working days its calculating one additional day, for example if I set the duration as e10d and the task start date is 01Dec2022, the end date should be 10Dec2022 right but its showing 11Dec2022 as end date.