Calculating complete dates with calendar days but....

I've brought this up before and at one point I thought that I found a great work-around. I would like to use calendar days in my date dalculations but, not have an end date land on a weekend. I created a complete column and with the use of a formula I tested the end date for a weekend and if it was a weekend I boosted it to Monday. However, the date calculation continues on with using the original end date. I know I can't place a formula on the calculated end date without turning-off Dependencies Enabled. It would be great if we had an option to use calendard days in the calculation and just not end on a weekend.

Find more posts tagged with

Calendar View

Formulas

Legal

Comments

Shaine Greenwood

Hello Jerry,

I'm not quite sure what you're asking, since dependencies is designed to not let tasks end on non-working days, which by default are Saturday and Sunday (although you can change this). Duration is automatically calculated based on the number of working days for a task.

With that being said, you might check out a few of our date functions, specifically NETWORKDAYS and WORKDAY: https://help.smartsheet.com/articles/775363-using-formulas#datefunctions

Jerry Tutt

Hi Shaine,

Therein lies the problem....If you define your work week as Monday - Friday then weekends will not be counted as work days. We want Saturday & Sunday included in the date calculation. We just don't want the end date to land on a Saturday or Sunday. If it does we want the end date to go to Monday. Am I confusing the issue?

Jerry Tutt

Shaine,

Let me give you an example. We may have a contract that specifies that we have to draft a Drainage Agreement and have it delivered to a Buyer within 15 days of the Effective Date of the Contract. Also, specified in the contract is that any date that falls on the weekend is to be moved to the following Monday. If the agreement is not delivered ontime the contract is void. This is a very, very common scenario.

Thanks,

jerrry

Shaine Greenwood

Hi Jerry—read you loud and clear.

Sounds like you want all days (working or non) to be counted, but you still want the automated push to the next available working day.

We don't have have this functionality in Smartsheet, but I've made sure to pass your feedback along to our Product team.

One idea would be to use the NETDAYS function in a column to get the total number of days, between your Start and Finish date columns. This can't be placed in your Duration column (dependencies won't allow for custom formulas), but you can create a Text/Number column and use a formula like the following:

=NETDAYS([Start Date]1 - [Finish Date]1)

This should take the difference between the two (regardless of whether they're working days or not) but you'll probably want to test this out in your sheet first.

Jerry Tutt

Thanks Shaine! Let me experiment with your suggestion. Thank you so much for your assistance. Have a wonderful Thanksgiving!

jerry

Shaine Greenwood

Any time! Enjoy your holidays

Ryan G

Hello, I'm curious if anything has been done to accomplish what the original poster was trying to achieve. I'm having the same problem and haven't found a work around that works other than to use a formula in a spreadsheet instead of the Gantt chart. This would be helpful for anyone who uses the software to manage contract related projects.

J. Craig Williams

Ryan,

If you want to modify the schedule using the Gantt chart, then no, no changes.

In addition, if the schedule sheet has Dependencies enabled, then

Smartsheet still does not allow:

(all references below to Date columns are on Sheets with Dependencies enabled)

a. formulas in Date columns

b. links to End/Finish Date columns

c. links to Start Date columns that also have Predecessors

d. formulas in Predecessor or Duration columns.

e. link to Predecessor column

and probably some other limitations I am forgetting before I finish my first cup of coffee for the day.

You can, however, link from another sheet to

a. Start Date column (see c above)

b. Duration column

Using these two, work-arounds can be built that accomplish the task. With Sheets without Dependencies the work-arounds are even easier.

Craig

Jerry Tutt

What I have been doing is to create a date column to adjust via a formula if the date falls on a weekend. I visually check this "Alert Date" to see if it disagrees with my calculated end date due to the end date falling on a weekend. I then adjust the duration to force the calculated end date to Monday that matches with my Alert Date. I then keep my Alert Date column hidden. The problem that I recent encountered with this solution is that the Smartsheet software has been modified so that all columns (hidden or otherwise) display on automated alerts. This causes a lot of confusion with end users, when they see things like "dependencies" & "Assigned to" that don't mean anything to them. I have requested this to be changed but, so far have not received any meaningful response.

Ryan G

Jerry,

Thanks for sharing your process. Are you able to add a column with a formula in a Gantt chart with dependencies turned on? I haven't been able to do so.

Ryan

Jerry Tutt

Ryan,

I have not tried to track a formula driven column on the Gantt. Actually we seldom use the Gantt portion of the Smartsheet. I usually produce it but, we don't actually use it as far as I know.

Jerry