Available on Amazon!
It only took me a year… and it grew from a little booklet to over 200 pages… but its finally done!
Bought the book? Post any questions, bugs, typos and suggestions to this blog post!
Everything you need to know about SharePoint formulas for SharePoint 2010 to 2019 and SharePoint Online / Office 365!
This book is for the SharePoint “power user” who needs a better understanding of how SharePoint formulas work, where they don’t work, and how they are created. While at first glance SharePoint formulas appear to be simple calculations, they are both quite powerful and have weird limitations. In this book we will explore the basics of creating Calculated Columns and Validation formulas with some boring details, and over one hundred examples. We also explore workarounds for many of the limitations by using SharePoint Designer workflows and a few tricks!
Over 100 Examples!
A how-to book of formulas would not be too useful without a few examples. I've been collecting these for years. They've come from classroom questions, forum questions, and my own SharePoint sites. Now they are all in one place…
- · Over 60 Calculated Columns examples
- · Over 30 Column Validation examples
- · 11 List/Library Item Validation examples
(and every one of the Column Validation examples can be used here.)
- · 7 Calculated Column Default Values examples
- · 15 Workflow “workarounds” for things SharePoint formulas can’t do
Scroll on down for a complete list of the examples.
Who is this book for?
Anyone who creates and customizes lists and libraries. These include: SharePoint on premise Farm Administrators, Office 365 SharePoint administrators, Site Collection Administrators, Site Owners, Power users, and Developers.
Where are formulas used in SharePoint?
- Calculated column formulas derive data from other columns in the same list item. These can be simple calculations and text manipulations, or complex Excel-style formulas.
- Column validation formulas are used to create custom validations for user entered data. These can be used to make sure that "quantities" are not negative and vacation requests are for dates in the future.
- Column default formulas, while very limited, can auto-fill columns with a date 30 days in the future or a message based on the month or day of the week the item was added.
- List / Library validation formulas are used to check the values from multiple columns to verify the relationship between two or more columns. Examples include making sure a task start date is before the task end date, or to make sure an entry has a "price per pound" or a "price each", but not both.
Workflow Workarounds?
SharePoint formulas only work with a few column types while SharePoint workflows can access just about any column type. So, with a little workflow work your formulas can make these columns available to SharePoint formulas. There's over fifty pages of workflow workaround tips:
- Workaround for People and Groups
- Getting Data from a Person or Group Column
- Getting a User’s Manager
- Workaround for the SUBSTITUTE Function
- Workaround for Getting View Totals from Calculated Columns.
- Workaround for Adding Images to a Column
- Workaround for Multiple Lines of Text
- Workaround for Lookup Columns
- Lookup a Single Item (No checkboxes)
- Extract Several Lookup Columns into a Single Line of Text
- Workaround for Multivalued Choice Columns
- Counting Items Selected in a Multivalued Column
- Workaround for Managed Metadata columns
- Workaround for Single Valued Managed Metadata Columns
- Workaround for Multi Valued Managed Metadata Columns
- Workarounds for Attachments
Table of Contents
- Read Me First
- Tips for Formulas
- Calculated Columns
- Calculated Column Examples
- Calculated Default Columns
- Column Validation Formulas
- Column Validation Examples
- List/Library Item Validation
- List/Library Item Validation Examples
- Other Forms of Validation
- Workflow Workarounds
- Error Messages
Examples:
Calculated Column Examples:·
· Fun stuff that may not work for you:
o Add HTML to a Calculated Column
o Add icons or pictures to a Calculated Column
o Building an Address (String Concatenation)
o Create a Bar Chart Column
· Columns for Views
o View Filtering on a Calculated Column
o Group by Year
o Group by Month and Year
o Group by Year Plus Month
o Grouping on an Algorithm
· Numbers
o Adding Leading Zeros to a Number
o Scientific Notation
o Roman Numerals
· The IF Function and Boolean Logic
o Calculating a Discount using Nested IFs
o Working Around Nested IF Limits
o Convert from State Codes to State Names
o ANDs and ORs - Approve if all approved, or reject if any one rejects
· Test for values
o Testing for a Range of Dates
o Testing for Empty Columns
o Testing for Errors
· Summing and Counting Columns
o Counting Yes/No Columns
o Average
o MIN and MAX
· More on Numbers
o Raise a Number to a Power
o Rounding Numbers
· Working with Text
o Combining Text Columns
o Display First Name and Last Name in a Single Column
o Creating Title Capitalization
· Return the Right Data Type!
· Dates
o Subtracting Dates
o Finding a Date One Year in the Future
o Change Date Formatting
o Fiscal Year
o Week Number
o Day of the Year
o First Day of the Current Month
o First Day of the Next Month
o Last Day of the Current Month
o Last Day of the Previous Month
o Last Day of the Next Month
o Third Tuesday of a Month
o Skipping Weekend Days
o Next Workday “X” Days in the Future
o Simple Solutions for 5, 10, 15 Working Days in the Future
o Solution for Any Number of Working Days in the Future
o Working Days
o Number of Working Hours Between Two Dates
· Formatting and Conversions
o Formatting Numbers
o Adding Text to Numbers
o Adding Special Symbols (¥, £, etc.) to TEXT() Number Formats
o Converting Numbers to Fractions
o Financial Calculations
· Random Numbers and Messages
o Creating Random Numbers (Using NOW)
o Creating Random Messages (using CHOOSE)
· A Calculated Task Status
· Great Circle Distance from Longitude and Latitude
· Simplify a Workflow by Using a Calculated Column
Column Validation Examples
· Limit time for user updates.
· Boolean operations
o Examples Using “OR”
o Examples Using “AND”
o Examples Using “AND” and “OR”
o Examples Using “NOT”
· Testing for Empty Columns
o Yes/No
o Dates
o ISBLANK()
· Working with Dates
o Date Must be in the Future
o Date Must be in the Future “x” Days
o Date Must be in the Future “x” Days and not on a Weekend
o Test Against a Specified Date
o Date Must be Between Now and the End of the Current Year
o Date Must be Within the Next 30 days
o Date Must be the Last Day of the Month
o Date Must be the First Day of the Month
o Date Must be the Third Tuesday of the Month
o Date Can Only be a Weekday
o Date Can Only be Monday or Wednesday
o Entered Date Must be for this Year
o Date Must be Current Or Next Month Only
· Working with Numbers
o Testing for Numbers in Custom Increments
o Limit the Number of Decimal Places Entered
· Working with Text
o Testing for Text Length
o Testing for a Valid State/Province (or other code)
o Test to Require an Uppercase or Lowercase Letter
· Examples Using Pattern Matching
o Must start with an "a" or "A" and the third character must be a "c" or "C"
o Match a Phone Number Pattern of xxx-xxx-xxxx
o Match a Phone Number Pattern of xxx-xxx-xxxx and Limit the Length
o Match a Phone Number and Make Sure Only Digits Have Been Used
And many more!
