Forum Discussion
Solved
Calculating % Progress for Multiple Start and End Dates Formula
Hi All,
My goal is to show the current percentage of progress between start and end dates.
Using the formula below works great, except I get the #NUM! error when a date is greater than today (line 2 in snapshot).
=MIN((DATEDIF(E14, TODAY(),"d")+1)/(DATEDIF(E14, F14, "d")+1), 100%)
I tried to use the IF formula below (line 4 in snapshot), but I lose the current percentage of progress of start and end dates since it only shows as 0%.
=IF(AND(E14 < TODAY(),F14 < TODAY()),1,IFERROR((DATEDIF(E14,TODAY(),"d")+1)/(DATEDIF(E14,F14,"d")+1),0))
Can someone help me correct this formula to reflect the current percentage of progress of start and end dates, without getting an error for future dates and still reflect its current percentage completed?
I'm sure its something easy but I have failed nesting it properly. Any help would be greatly appreciated!
- 3 odowiedzi.
mtarler Thanks for the quick response, I used your formula and it works great!
=IF(E14 < TODAY(),(DATEDIF(E14,TODAY(),"d")+1)/(DATEDIF(E14,F14,"d")+1),0)However, I just noticed when the date has elapsed and is in the past, it's showing a value exceeding 100%. How do I get the progress bar to reflect 100% when the date has passed?
