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.

Add workdays to date

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?

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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

  • Patty Wu
    Patty Wu
    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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.

     

     

     

     

     

     

     

     

     

  • Patty Wu
    Options

    Craig,

    Thank you so much!

    Your formulas solved all my problems and they work well!

     

This discussion has been closed.