Formulas & functions not working

01/08/21
Accepted

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 CronkMark Cronk ✭✭✭✭✭
    Accepted 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

Answers

  • Mark CronkMark 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

  • 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 CronkMark 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

  • Hello @Mark Cronk

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

    Please advise

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

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi @Matthew Salama ,

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

    Mark

Sign In or Register to comment.