SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2899528%22%20slang%3D%22en-US%22%3EHelp%20please%20-%20Time%20elapsed%20as%20percentage%20with%20100%25%20cap%20and%20no%20negative%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899528%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20below%20table%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Martha_B_0-1635498895328.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321270i0C845A82FBFEAFBF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Martha_B_0-1635498895328.png%22%20alt%3D%22Martha_B_0-1635498895328.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20the%20Percent%20Completed%20column%20to%20show%20the%20time%20elapsed%20as%20a%20percentage%2C%20but%20with%20the%20following%20features%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECapping%20at%20100%25%20for%20tasks%20whose%20end%20date%20has%20passed%3C%2FP%3E%3CP%3ENot%20showing%20negative%20values%20for%20tasks%20not%20yet%20started%3C%2FP%3E%3CP%3ERemaining%20blank%20if%20no%20date%20is%20available%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20playing%20with%20IFs%20for%20ages%20this%20morning%2C%20but%20can't%20seem%20to%20crack%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMartha%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2899528%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899735%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%20-%20Time%20elapsed%20as%20percentage%20with%20100%25%20cap%20and%20no%20negative%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199564%22%20target%3D%22_blank%22%3E%40Martha_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20G4%3A%3C%2FP%3E%0A%3CP%3E%3DIF(OR(C4%3D%22%22%2CD4%3D%22%22)%2C%22%22%2CMIN(MAX((TODAY()-C4%2B1)%2F(D4-C4%2B1)%2C0%25)%2C100%25))%3C%2FP%3E%0A%3CP%3EFormat%20as%20a%20percentage%20and%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899801%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%20-%20Time%20elapsed%20as%20percentage%20with%20100%25%20cap%20and%20no%20negative%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BI'm%20afraid%20that's%20not%20working%20-%20I'm%20getting%20100%25%20in%20some%20tasks%20not%20started%20or%20with%20dates%20in%20the%20future%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Martha_B_0-1635504222471.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321301i22F9C5419D22968F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Martha_B_0-1635504222471.png%22%20alt%3D%22Martha_B_0-1635504222471.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899812%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%20-%20Time%20elapsed%20as%20percentage%20with%20100%25%20cap%20and%20no%20negative%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199564%22%20target%3D%22_blank%22%3E%40Martha_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20small%20sample%20workbook%20demonstrating%20the%20problem%2C%20without%20sensitive%20information%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899815%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%20-%20Time%20elapsed%20as%20percentage%20with%20100%25%20cap%20and%20no%20negative%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2900552%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%20-%20Time%20elapsed%20as%20percentage%20with%20100%25%20cap%20and%20no%20negative%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2900552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199564%22%20target%3D%22_blank%22%3E%40Martha_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20that%20I%20posted%20was%20for%20row%204%20(as%20I%20mentioned)%2C%20not%20for%20row%202.%3C%2FP%3E%0A%3CP%3EI%20have%20attached%20a%20version%20with%20structured%20table%20references.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.