Completion percentage formula

[rlexcel101]

Hey guys questions on percentage formula between two dates so I have:

Tested two formulas:

1) IFERROR(IF(TODAY()>=X6,””,MAX((TODAY()-W6)/MAX(X6-W6,1),0)),””)

X6 beingend date
W6 being start date

Issue with this is i don’t receive any “100%” if the date is already completed

2) =(W6-TODAY())/(W6-X6)

Issue with this one is I get the percentages but I get a DIV error and if the date has already past (say completion date listed is 7/1/2018) I get like 120% where I just want 100%


So basically I want a formula that reads if date has passed then it just states 100% and for it to read if no values than display nothing.

 

[Scott Huish]

Not sure what you are trying to do exactly, but is this what you want?

=IF(COUNT(W6:X6)<2,"",MIN(1,(W6-TODAY())/(W6-X6)))