Entering Formulas for Calculated Fields in Lists

A few pointers to those creating calculated columns/fields in SharePoint 2010.

  1. When entering a formula when creating the field initially, it has to have no syntax errors otherwise you will get a nasty error page! If you get this error clicking the back button will take you back to where you were.
  2. Another way to to create the field with a dummy formula and edit the formula by displaying the list in the data sheet view mode. In SharePoint 2010 this is greatly improved! Its also a great way to debug your formula if it is complex.
  3. If you have a very complex formula – below is one I created – you could construct it in say NotePad in parts to help reduce errors while creating your formula.

=IF(ISERROR(SEARCH(” hrs”,Duration,1)), IF(ISERROR(SEARCH(” mins”,Duration,1)), TIME(HOUR(Duration),MINUTE(Duration),0), TIME(HOUR(0),MINUTE(“0:”&REPLACE(Duration, SEARCH(” mins”,Duration,1),5,””)),0)), TIME(HOUR(REPLACE(Duration, SEARCH(” hrs”,Duration,1),4,””)),MINUTE(REPLACE(Duration, SEARCH(” hrs”,Duration,1),4,””)),0))*24


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s