SOLVED

# calculating percentage between two dates

Copper Contributor

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

10 Replies

# Re: calculating percentage between two dates

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

# Re: calculating percentage between two dates

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"?

# Re: calculating percentage between two dates

@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

# Re: calculating percentage between two dates

The formula you showed would not work for me. It says there is an Inconsistent calculated column formula.

I double checked and the version of Excel I am using is
Microsoft® Excel® for Microsoft 365 MSO (Version 2305 Build 16.0.16501.20074) 64-bit
would that have anything to do with why I can't get your formula to work?

# Re: calculating percentage between two dates

It is not recognizing this string as a formula
IFERROR(MIN(1; (DATEDIF(B4;TODAY();"d")+1)/(DATEDIF(B4;C4;"d")+1));"not started")
it's thinking its just plain text. am I missing something?

# Re: calculating percentage between two dates

Strange.....did you include "=" at the beginning of the formula?.. I mean....."=IFERROR(MIN..."

# Re: calculating percentage between two dates

yes I did.  I can't figure out what I'm doing wrong.
=IFERROR(MIN(1; (DATEDIF(B2;TODAY();"d")+1)/(DATEDIF(B2;C2;"d")+1));"not started")

This formula gives me this error

best response confirmed by creativekat26 (Copper Contributor)
Solution

# Re: calculating percentage between two dates

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

# Re: calculating percentage between two dates

IT WORKED!!!!!!!!!!!!! Thank you SOOOOOOOOOOOO Much!

# Re: calculating percentage between two dates

1 best response

Accepted Solutions
best response confirmed by creativekat26 (Copper Contributor)
Solution

# Re: calculating percentage between two dates

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