Fun with SharePoint Calculated Columns

Here are a couple of formulae I recently created. The first one tests for blank values then adds to existing date.

Revision Due Date:

=IF(ISBLANK(EntityRevisionRequested),IF(ISBLANK(ReturnedforRevision),””,ReturnedforRevision+5),EntityRevisionRequested+5)

The second formula evaluates numerous other columns and returns values on business rules. Things to note about this formula¬†are the handling of “Permit Sketch Required” column. As this column is a Yes / No data type it is not tested for being null, but as true or false.

Also note that their is an AND clause to test multiple values embedded as well.

Permit Status Bucket:

=IF(ISBLANK(PermitApproved),IF(AND(NOT(ISBLANK(ReturnedforRevision)),NOT(ISBLANK(RevisionComplete))),”Revision Complete; Permit Pending”,IF(AND(NOT(ISBLANK(EntityRevisionRequested)),NOT(ISBLANK(RevisionComplete))),”Revision Complete; Permit Pending”,IF(NOT(ISBLANK(ReturnedforRevision)),”Revision Requested”,IF(NOT(ISBLANK(EntityRevisionRequested)),”Revision Requested”,IF(NOT(ISBLANK(PermitSubmitted)),”Permit Submitted; Pending Approval”,IF(NOT(ISBLANK(SubmittedtoROW)),”Pending Permit Submission”,IF([Permit Sketch Required],”Pending Sketch”,””))))))),”Permit Approved”)

Seven embedded IF Statements!