Forum Discussion

Brass Contributor

Feb 22, 2023

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 (...

Formulas and Functions

mtarler
Feb 22, 2023
normabeez add that MIN() part you had previously:
=IF(E14 < TODAY(),MIN((DATEDIF(E14,TODAY(),"d")+1)/(DATEDIF(E14,F14,"d")+1),1),0)

normabeez

Brass Contributor

Feb 22, 2023

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?

mtarler

Silver Contributor

Feb 22, 2023

normabeez add that MIN() part you had previously:

=IF(E14 < TODAY(),MIN((DATEDIF(E14,TODAY(),"d")+1)/(DATEDIF(E14,F14,"d")+1),1),0)

normabeez
Brass Contributor
Feb 22, 2023
This worked perfectly! Thank you so much for your help!