Formulas & functions not working

Matthew Salama

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

Mark Cronk

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

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

Matthew Salama

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

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

Matthew Salama

Hello @Mark Cronk

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

Please advise

Mark Cronk

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

Matthew Salama

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

Mark Cronk

Hi @Matthew Salama ,

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

Mark