Multiple working days calendar

Joseph Aloysias

All of our projects have two different departments involved, 1.Corporate (they work for 5 days(Monday to Friday)) + 2.Manufacturing (they work for 7days in a week) activities.

Corporate follows a 5-day calendar AND Manufacturing follows a 7-day calendar.

I would like to set a calendar based on the activity, say for example we have a task and it takes 10 days to finish,

• if the task is for corporate it should calculate 5 days in a week (if the task is started on Monday then this will finish on next Friday)
• if the task is for manufacturing it should calculate 7 days in a week (if the task is started on Monday then this will finish on next Wednesday)

How can I achieve this ?

Genevieve P.

Hi @Domnic Victor

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

Joseph Aloysias

@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)

Genevieve P.

Hi @Domnic Victor

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

Joseph Aloysias

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

Genevieve P.

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))

Joseph Aloysias

@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)

Genevieve P.

Hi @Domnic Victor

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))

Joseph Aloysias

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

Genevieve P.

Hi @Domnic Victor

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).

Joseph Aloysias

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

Genevieve P.

Hi @Domnic Victor

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.

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

Cheers,

Genevieve

Joseph Aloysias

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)?

Joseph Aloysias

@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.

Genevieve P.

Hi @Domnic Victor

In Resource Management yes, you can define working days by individuals (versus by project). Here's the article that goes through how to do this:

https://help.smartsheet.com/articles/2481291-people-availability

This will show that resource as unavailable on the days they do not have set as working days. I don't believe it would automatically adjust the duration as the working days for the "Project" are set as well, however it would help you easily identify who is available at different days/times and if a duration needs to be increased because of this.

In regards to the elapsed time, I believe this is because elapsed time does not take into account working hours in your Project Settings. This means that 1 elapsed day ends the next day, once the full day is complete. If you want the task to end on 10/12, you can set the elapsed time to e9d instead!

Cheers,

Genevieve