or Explore Discussions

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

Matthew

• 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

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

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