Excel adding IFERROR to progress statement (conditional formatting)-CodePudding

enter image description hereFor example I have a start date cell and an end date cell. I also have a conditional format statement that will take these two cells and calculate the progress in % of how complete my project is.

For example:

Bathroom Renovation: 
Start 11/7/2022
Finish: 11/9/2022
Progress 33%

Here is my excel code to calculate this:

=MIN(1, (DATEDIF(E11,TODAY(),"d") 1)/(DATEDIF(E11,F11,"d") 1))

I need to add an IFERROR (or some condition like) so that when there is no start or end date is says "Not Started" for example.

I have tried this:

=IFERROR((DATEDIF(B2,TODAY(),"d") 1)/(DATEDIF(B2,C2,"d") 1),"Not start").

That problem that I've run into with this is I am trying to cap the percentage at 100%, and the code statement I started with does that. When I enter this string of code, it works, however when a project is complete it will say 150%, 450%, etc. I need it to also cap at 100%

CodePudding user response：

The =MIN(1,"other code") part is capping the value at 100% in the first example and it is missing from the second. try:

=IFERROR(MIN(1,(DATEDIF(B2,TODAY(),"d") 1)/(DATEDIF(B2,C2,"d") 1)),"Not start")

CodePudding user response：

assuming E15 is your start date and F15 is your finish date, try:

=IF(ISBLANK(E15),"Not start",IFERROR(MIN(1,(DATEDIF(E15,TODAY(),"d") 1)/(DATEDIF(E15,F15,"d") 1)),"Not start"))

This will display the progress as not started if there is no start date.