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
-
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))))
Answers
-
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))))
-
Thank you very much for your help. I appreciate your time.
-
Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
