How to cap a cell at 100%

[Project217]

Hi.

Just trying to cap the percentage at 100% it will currently go past 100%
The code is =IFERROR((DATEDIF(M10,TODAY(),"d")+1)/(DATEDIF(M10,N10,"d")+1),"0%")
This gives me a percentage required for a task completion of the date entered in cells M10 and N10
Any Help Would Be Greatly appreciated

 

[Jimmypop]

Hi There

Have you tried... This is untested seeing as I am not getting any % value result from formula when I eneter dates...just maybe doing it wrong somewhere, but please test the below...

=MIN(100,IFERROR((DATEDIF(M10,TODAY(),"d")+1)/(DATEDIF(M10,N10,"d")+1),"0%"))

 

[Project217]

H

 

[Project217]

Hi There

Have you tried... This is untested seeing as I am not getting any % value result from formula when I eneter dates...just maybe doing it wrong somewhere, but please test the below...

=MIN(100,IFERROR((DATEDIF(M10,TODAY(),"d")+1)/(DATEDIF(M10,N10,"d")+1),"0%"))

 

[Project217]

Hi Jimmypop, Thanks for your reply, Unfortunately this didn't work The cell percentage Value still goes past 100%

 

[Jimmypop]

What is the criteria for the dates? Is it entered via a formula? maybe give an example of where the dates exceed 100

 

[StephenCrump]

=MIN(100,IFERROR((DATEDIF(M10,TODAY(),"d")+1)/(DATEDIF(M10,N10,"d")+1),"0%"))

Try Min(1, ....

i.e. 100%

 

[Project217]

Ok So the dates are for scheduled or planned start and completion of a task and depending on today's date is what percentage the task should be complete, This percentage is going over 100% Hope this makes sense

What is the criteria for the dates? Is it entered via a formula? maybe give an example of where the dates exceed 100

 

[Jimmypop]

Try it with Stephen suggestion... Thinking I made a rookie mistake hereand let us know

 

[Project217]

Try Min(1, ....

i.e. 100%

Yep Worked Perfectly.
Thanks lads.