Formulas & functions not working

Hello,


I am attempting to create a % Completion formula to track a projects completion b/w start and end dates. I cannot seem to make the functions work in the cells. Is this because I haven't upgraded to the full license or am I doing something wrong?

Please advise,

Matthew

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Sorry about that. I found an error - missing paren. Try this:

    =IF(AND(ISDATE([start date]@row), ISDATE([end date]@row)), IF([start date]@row>today(), 0, IF([end date]@row<today(), 1, NETWORKDAYS([start date]@row, today()) / NETWORKDAYS([Start date]@row, [end date]@row))), 0)

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Matthew Salama ,

    Does your license allow you to enter other formulas or only data?

    If you can enter other formulas, please attach a screenshot of your sheet and the formula that's not working. Remove or hide any sensitive information.

    Happy to help.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • This is what the formula is giving me as a % total. Theoretically it should be 100%. How can I show this and make the argument valid for the remainder of my sheet?

    Note: some project deadlines have not yet lapsed.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Matthew Salama ,

    I'd adjust your formula to prevent errors. Use:

    =IF(AND(ISDATE([start date]@row), ISDATE([end date]@row)), IF([start date]@row>today(), 0, IF( [end date]@row<today(),1, NETWORKDAYS([Start Date]@row, today())/NETWORKDAYS([Start date]@row, [end date]@row)), 0)

    This will work as a column formula. The formula says that if a date is missing the % complete is 0%. If the start date is in the future % complete is 0%. If the end date is in the past % complete is 100%. Otherwise calculate the % complete.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Hello @Mark Cronk

    I applied the formula and the cell now states #Incorrect Argument.

    Please advise

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Sorry about that. I found an error - missing paren. Try this:

    =IF(AND(ISDATE([start date]@row), ISDATE([end date]@row)), IF([start date]@row>today(), 0, IF([end date]@row<today(), 1, NETWORKDAYS([start date]@row, today()) / NETWORKDAYS([Start date]@row, [end date]@row))), 0)

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thank you so much @Mark Cronk for your amazing help!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Matthew Salama ,

    Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!