Percent Complete Between Two Dates - When Not Started How To Show Zero Percent

[Consult123]

Hello,

I have formula that automatically calculates the percentage of completion on a project between two dates (the start date and end date). The formula I chose to use is: =MIN((DATEDIF(start date column, TODAY(),"d")+1)/(DATEDIF(start date column, end date column, "d")+1),100%). However, when the task has not started or is in the future, the formula breaks and says #NUM!. How do I make that cell say 0%?

Likewise, what are the best conditional formatting formulas for column F?
-On Track (Trigger- Based on current date, the task will complete on time by due date)
-Complete (Trigger- Column K at 100%)
-At Risk (Trigger- Based on current date, task will not complete on time by due date)
-Off Track (Trigger- Based on current date, task will not complete on time by due date)
-In Progress (Trigger- Column K >1%)
-Not Started (Trigger- Column K at 0%)
-Cancelled (Trigger- Columns D and E not populated with date)

@Joe4

 

[DanteAmor]

Try:

=IFERROR(MIN((DATEDIF(start date column, TODAY(),"d")+1)/(DATEDIF(start date column, end date column, "d")+1),100%), 0)

 

[Consult123]

Try:

=IFERROR(MIN((DATEDIF(start date column, TODAY(),"d")+1)/(DATEDIF(start date column, end date column, "d")+1),100%), 0)

This worked! Thanks so much. Are you able to assist with the conditional formatting formulas?