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

FusiaSuede

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)))))))

Paul Newcome

Give this line of thought a try...

You are trying to basically say that if there is a specific status, don't go over 75%.

So to stop it at 75% you say that if the original formula is greater than or equal to .75, then output .75, otherwise use whatever the original formula would output...

=IF(original formula >= .75, .75, original formula)

But you only want it to stop if it is greater than or equal to AND there is a specific status.

=IF(AND(specific status, original formula >= .75), .75, original formula)

But there are two different statuses this could be applicable to: "On Hold" OR "In Progress".

=IF(AND(OR(Status@row = "On Hold", Status@row = "In Progress"), original formula >= .75), .75, original formula)

I went ahead and removed some unnecessary parenthesis from your formula. Dropping that into the above layout would end up looking like this...

=IF(AND(OR(Status@row = "On Hold", Status@row = "In Progress"), IF(ISBLANK([Proj. Task Comp.]4), "", IF(TODAY() > [Proj. Task Comp.]@row, 1, IF(TODAY() < [Proj. Task Launch]@row, 0, NETWORKDAYS([Proj. Task Launch]@row, TODAY()) / NETWORKDAYS([Proj. Task Launch]@row, [Proj. Task Comp.]@row))) >= .75), .75, IF(ISBLANK([Proj. Task Comp.]4), "", IF(TODAY() > [Proj. Task Comp.]@row, 1, IF(TODAY() < [Proj. Task Launch]@row, 0, NETWORKDAYS([Proj. Task Launch]@row, TODAY()) / NETWORKDAYS([Proj. Task Launch]@row, [Proj. Task Comp.]@row))))

Paul Newcome

Give this line of thought a try...

You are trying to basically say that if there is a specific status, don't go over 75%.

So to stop it at 75% you say that if the original formula is greater than or equal to .75, then output .75, otherwise use whatever the original formula would output...

=IF(original formula >= .75, .75, original formula)

But you only want it to stop if it is greater than or equal to AND there is a specific status.

=IF(AND(specific status, original formula >= .75), .75, original formula)

But there are two different statuses this could be applicable to: "On Hold" OR "In Progress".

=IF(AND(OR(Status@row = "On Hold", Status@row = "In Progress"), original formula >= .75), .75, original formula)

I went ahead and removed some unnecessary parenthesis from your formula. Dropping that into the above layout would end up looking like this...

=IF(AND(OR(Status@row = "On Hold", Status@row = "In Progress"), IF(ISBLANK([Proj. Task Comp.]4), "", IF(TODAY() > [Proj. Task Comp.]@row, 1, IF(TODAY() < [Proj. Task Launch]@row, 0, NETWORKDAYS([Proj. Task Launch]@row, TODAY()) / NETWORKDAYS([Proj. Task Launch]@row, [Proj. Task Comp.]@row))) >= .75), .75, IF(ISBLANK([Proj. Task Comp.]4), "", IF(TODAY() > [Proj. Task Comp.]@row, 1, IF(TODAY() < [Proj. Task Launch]@row, 0, NETWORKDAYS([Proj. Task Launch]@row, TODAY()) / NETWORKDAYS([Proj. Task Launch]@row, [Proj. Task Comp.]@row))))

FusiaSuede

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

Paul Newcome

Happy to help! 👍️