Tagged: Lists

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