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

Options

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?

• ✭✭✭✭✭✭
edited 05/03/17
Options

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

• edited 05/04/17
Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

Craig,

Thank you so much!

Your formulas solved all my problems and they work well!

This discussion has been closed.