Adjusting Formula to stop at 100% and not go over.

[Walkerwood9]

I have a formula in a macro I am trying to adjust to not go over 100% like it currently does. It currently looks at a start and finish date and calculates a percentage of what should be done, but the only problem is that is goes over 100% when I want it to stop at 100% and not go over. Below is the code.



Range("P21").Select
Application.CutCopyMode = False
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]<TODAY(),ROUND((DATEDIF(RC[-2],TODAY(),""D"")+1)/(DATEDIF(RC[-2],RC[-1],""D"")+1),2),0)"
Range("P21").Select
Selection.Style = "Percent"

 

[Peter_SSs]

Try this as your formula

"=IF(RC[-2]<TODAY(),MIN(ROUND((DATEDIF(RC[-2],TODAY(),""D"")+1)/(DATEDIF(RC[-2],RC[-1],""D"")+1),2),1),0)"