Add workdays to date

Patty Wu

There are some columns that will be used to manage my project, "% completion", "Start Date","End Date", "Duration","Today".

Now, our tasks show their completion percentage and I can see in the Gantt Chart how far the tasks run in the entire time and some of tasks seems like a bit delay from today. Therefore, I want to set a condition to show which tasks are delay from today. I use "% completion(percentage)" * "Duration" to convert the completion percentage to days.

For example, 9%*123 days= 11.07 days, which means the task runs 11.07 days within 123 days.

Then, I add the "Start Date" with the previous result. And thus I can compare this result with Today's date.

For Example, 2017/4/10+11.07 days=2017/4/21

However, this result will contains non-working days. How should I do if I only want workdays when the days add to the date? or if there is any other solution for this situation?

Mike Wilday

To calculate the answer you will need to use the =networkdays formula to compare the two dates and get working days. Do you have a formula that you have already started working on that we can review and revise for you? 

This link could be of help: https://help.smartsheet.com/articles/775363-using-formulas#networkdays

Patty Wu

Below is the formula that I used right now.

=NETWORKDAYS([Start Date]1,[End Date]1)*[%Completion]1

(The data type here is Text/Number)

This result shows the task runs 11.07 days within 123 days.(The column called "%Day")

Then I use this formula to add the days to the start date.(The column called "%Date")

=WORKDAY([Start Date]1,[%Day]

(It should show a date and my data type here is Date.)

And thus it shows error(invalid data type) here.

I assume that the decimal causes this error. 

I have to manually enter the day that I calculate in "%Day".

For example, it the result of %Day is 11.07 and I enter 11.

Then it can show the result I need.

Is that any solution to solve this problem?

Or is that any possible to combine these two formula?

Thanks

J. Craig Williams

Patty,

Your formula is not complete. I don't know if that was a copy/paste error* or a real error.

This:

=WORKDAY([Start Date]1, [%Day]1)

would complete it, but you are correct,  the second argument needs to be an integer.

If you are wish to round down, then this

=WORKDAY([Start Date]1, INT(%Day1))

if you wish to round nearest, then this

=WORKDAY([Start Date]1, ROUND(%Day1, 0))

 

To combine into a single formula, just replace the %Day1 with your formula from that cell

=WORKDAY([Start Date]1, ROUND(NETWORKDAYS([Start Date]1, [End Date]1) * [% Complete]1, 0))

Hope that helps.

Craig

* copy/paste is the work of the Devil.

 

 

 

 

 

 

 

 

 

Patty Wu

Craig,

Thank you so much!

Your formulas solved all my problems and they work well!