SOLVED

Help please - Time elapsed as percentage with 100% cap and no negative values

Copper Contributor

Hi all, 

 

I have the below table:

 

Martha_B_0-1635498895328.png

 

I would like the Percent Completed column to show the time elapsed as a percentage, but with the following features:

 

Capping at 100% for tasks whose end date has passed

Not showing negative values for tasks not yet started

Remaining blank if no date is available

 

I have been playing with IFs for ages this morning, but can't seem to crack it. 

 

Thanks!

 

Martha 

6 Replies

@Martha_B 

In G4:

=IF(OR(C4="",D4=""),"",MIN(MAX((TODAY()-C4+1)/(D4-C4+1),0%),100%))

Format as a percentage and fill down.

@Hans Vogelaar I'm afraid that's not working - I'm getting 100% in some tasks not started or with dates in the future

 

Martha_B_0-1635504222471.png

 

@Martha_B 

Could you attach a small sample workbook demonstrating the problem, without sensitive information?

best response confirmed by Martha_B (Copper Contributor)
Solution

@Martha_B 

The formula that I posted was for row 4 (as I mentioned), not for row 2.

I have attached a version with structured table references.

1 best response

Accepted Solutions
best response confirmed by Martha_B (Copper Contributor)
Solution

@Martha_B 

The formula that I posted was for row 4 (as I mentioned), not for row 2.

I have attached a version with structured table references.

View solution in original post