One of our previous SharePoint Developer developed this formula for a calculated column named as "pGantt" in a SP2010 document library. I am not able to understand its syntax. The value produced by the formula is in always percentage eg. 130%, 110%, 90%, etc. Kindly explain what exactly the formula is meant for.

Many thanks in advance.

Here is the code: =IF(ISERROR(IF(ISERROR(DATEDIF(CommencementDate,Today,"m")),0,DATEDIF(CommencementDate,Today,"m"))/IF(ISERROR(DATEDIF(CommencementDate,ExpiryDate,"m")),0,DATEDIF(CommencementDate,ExpiryDate,"m"))),1,IF(ISERROR(DATEDIF(CommencementDate,Today,"m")),0,DATEDIF(CommencementDate,Today,"m"))/IF(ISERROR(DATEDIF(CommencementDate,ExpiryDate,"m")),0,DATEDIF(CommencementDate,ExpiryDate,"m")))

1 Answer 1


It calculates

(Months from CommencementDate to today)/(Months from CommencementDate to ExpiryDate)

Ie how expired the document is.

Most of it is for handling problems with data.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.