Stopping %Complete if end date is past but Task is incomplete


Good morning,

After a long search I am finally reaching out.

In my % Complete column I am using an IF(ISBLANK w/ Networkdays formula to get my percent complete number; Pulling dates from 'Proj. Task Launch' & 'Proj. Task Comp.'

I can't seem to find the right formula to stop the '% Complete' (lets say at 75%) if the 'Proj. Task Comp.' date is past but the task is still "in progress", or "on hold" until I enter the 'Actual Comp. Date', or select "Complete" in Status column.

Below is my current formula. Thank you in advance.

IF(ISBLANK([Proj. Task Comp.]4), "", IF(TODAY() > [Proj. Task Comp.]4, 1, IF(TODAY() < [Proj. Task Launch]4, 0, ((NETWORKDAYS([Proj. Task Launch]4, TODAY()) / (NETWORKDAYS([Proj. Task Launch]4, [Proj. Task Comp.]4)))))))

Best Answer


  • FusiaSuede

    Thank you very much for your help. I appreciate your time.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!