Forum Discussion
Solved
calculating percentage between two dates
Hello -
I am trying to show a percentage complete between two dates. I am using the formula:
=MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1))
which is working great and leaves the percentage at 100% once the end date has passed.
However, for dates in the future it is returning a #NUM! error. How can I use the formula above and also combine something that will show a "0" instead of an error in my spreadsheet?
I know the following code would work,
=IFERROR((DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1),"Not start")
but I just don't know how to combine the two to make it display the way I want it to.
creativekat26 I think the problem is the separators between the function arguments. The example I've provided is ";"(this is my separator). If you have ","(comma) as a separator please replace ";" with "," in my formula.
Hi creativekat26 the formula proposed by you works. Which version of excel do you have?
'IFERROR(MIN(1; (DATEDIF(B4;TODAY();"d")+1)/(DATEDIF(B4;C4;"d")+1));"not started")
Regards
leoperdia I have the latest version of excel.
I know that each formula works separately - but is there a way to combine these two formulas below?
=IFERROR((DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1),"Not start")
=MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1))
So that when a date passes it shows 100% and for future dates it shows "not started"?
Thank you for your help!
creativekat26 Meaby I do not get you correctly, but the formula I've shared in the previous post is a combination of both:
IFERROR(MIN(1; (DATEDIF(B4;TODAY();"d")+1)/(DATEDIF(B4;C4;"d")+1));"not started")
The example I've shared is using this formula. The behavior is the following:
-If range date in the future--> not started
-If today is within the range date---> % of progress
-If range date in the past--> 100%
Regards
https://www.upwork.com/freelancers/~01cf0fc8446b00f44c
