Percentage (%) complete formula - cancellation date

veenz20

Hello,

Due to COVID 19 we have had to cancel some courses up till the end of June.

I need help to make a column with a formula to calculate the percentage of the course that was complete at time of course cancellation (any date between start and end date) given columns with the start date, end date and cancellation date.

Any help gratefully appreciated, thanks in advance.

Paul Newcome

Are you able to provide a screenshot that has sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed? I am sure there is a solution, but being able to visualize what you are working with would really help.

Venus Paikea

hello, I’m unable to send an exact screenshot, however I have put together a very simple version of the spreadsheet with only one line of text. The actual spreadsheet has around 100 courses that were still running which had to be cancelled last week due to COVID 19 lockdown. My boss wants a column that shows the percentage of the completed part of the course at the actual date the course was cancelled.

Paul Newcome

Ok. So the basic syntax formula you are going to end up using is going to look something like this...

=([Current Date]@row - [Start Date]@row) / ([End Date]@row - [Start Date]@row)

The part we need to solve for is [Current Date]@row. So basically we want to input the [Cancelled Date]@row if there is one. If there is not, then we want to use TODAY(). Do I have that part right? If so, an IF statement would work for this.

=IF(ISDATE([Cancelled Date]@row), [Cancelled Date]@row, TODAY())

Now that we know how to populate that first date, we can drop the whole thing into our general syntax.

=(IF(ISDATE([Cancelled Date]@row), [Cancelled Date]@row, TODAY()) - [Start Date]@row) / ([End Date]@row - [Start Date]@row)

Stacey Carrasco

@Paul Newcome what if I want ss to calculate the % Complete column automatically based on today's date in relation to the start and finish date columns.

Paul Newcome

@Stacey Carrasco You would end up using something very similar to the first formula above.

=(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)

Stacey Carrasco

@Paul Newcome your formula above results in a negative number if the date is in the future.

I'm using this formula: =IF(TODAY() > [End Date]@row, 1, IF(TODAY() < [Start Date]@row, 0, ((NETWORKDAY([Start Date]@row, TODAY()) / (NETWORKDAY([Start Date]@row, [End Date]@row))))))

The above formula seems to work although now I'm curious how can you get the progress bar to overlay in the gantt view.

Thanks for your help.

Paul Newcome

Yes. It will produce a negative if the [Start Date] is in the future. Because there are so many different variations/variables, I figured I would give you the starting point and then get more details from you if that didn't work.

But it looks like you figured it out on your own. Well Done. 👍️

As for getting the % complete to overlay in Gantt view...

If you go into the project settings, you can set which columns are used for the start date, end date, and % Complete and the %Complete will still show with dependencies turned off.

Stacey Carrasco

@Paul Newcome When I went into Project Settings and made the changes removing the check box from Dependencies I noticed that the formula was removed from my % Complete column

Paul Newcome

That's odd. Try putting the formula back in and see if it sticks.

Stacey Carrasco

@Paul Newcome I removed dependencies but now it seems I've lost dependency features as well that assists in the duration period. Maybe I can only have one or the other but not both dependencies and the %complete to indicate on the gantt chart?

Stacey Carrasco

@Paul Newcome I think it works now. I'm not quite sure what I did but I think it looks correct. In Project Settings I indicated None in the % Complete column and I think the Gantt looks correct.

Thanks for your help!

Paul Newcome

If you have dependencies turned on and indicate none in the % Complete, then there shouldn't be any overlay on the Gantt.

When dependencies are turned on, you cannot have formulas in any column referenced by the dependency settings.