Weekly Newsletter Signup

• Upcoming
  Live Online Workshops

  • Excel and SharePoint: I didn’t know you could do that!
  • Easy Tabs Interface - Simple SharePoint Navigation for Complex Information
  • Train the Trainer: Materials and Resources for in-house SharePoint Training
  • Become Your Company’s SharePoint SuperStar!
  • Hack SharePoint Web Part Displays with XSLT
  • Business Solution: Health Care Case Management
  • Killer Calendars in SharePoint
  • The Basics of Content Types
  • Business Solution: Create a Compliance Report Intake System in SharePoint
  • Business Solution: Manage Input with Dynamic Feedback Forms
  • Live Blogging at SharePoint Conference 2009

• Ongoing Series

  • Article Archives (1000+)
    All Authors
  • This Week in SharePoint with Joel
    Joel Oleson
  • jQuery for Everyone
    Paul Grenier
  • The Calculated Column
    Dessie Lunsford
  • The Data View Web Part
    Laura Rogers
  • Stump the Panel: Q&A
  • Subscribe to the Weekly Newsletter

• Authors

  • Dessie Lunsford
  • Laura Rogers
  • Lee Reed
  • Mark Miller
  • Jim Bob Howard
  • Paul Galvin
  • Paul Grenier
  • Toni Frankola
  • Woody Windischman

• SharePoint Resources

  • Bits of SharePoint
  • Codeless Solutions for SharePoint
  • Get the Point
  • Innovation Matters
  • Path to SharePoint
  • Paul Galvin
  • SharePoint 911
  • SharePoint Blank
  • SharePoint Blogs at SharePointU
  • SharePoint for Non-Developers
  • SharePoint Forums at SharePointU
  • SharePoint Screencasts
  • SharePoint Use Cases
  • Social Media Talk Tweets
  • The Sanity Point
  • The Weekly Newsletter  

Probably the best approach to really be able to see just what’s going on is to plug in a few test dates and run through the calculations, which is what we’ll begin with next time.

I wanted to start off the series on the “Date and Time” set of functions by examining a specific problem that many folks have posted questions about – that is, how do you calculate the difference (number of days) between two dates, while excluding weekends? (Iain on the STP forums asked this recently, which sparked my interest in writing this)

If we had taken the same direction as in the previous series (detailing the “IF”) to test for each possible combination of values with multiple nested “IF” functions, the formula would have been considerable longer, and would have worked, but would have been much less efficient.
By using the “OR” and “AND” functions to test for combinations of values at the same time, we’ve not only lessened the development time need to create the formula, we’ve also made debugging and testing much easier (always a good thing).

In order to take what we’ve learned by using our “Truth Table” and convert it into a usable formula for our SharePoint list, we need to work through the logic in order to make sure we don’t skip any steps (which can happen when designing complicated formulas “on-the-fly”). In the previous series on “Logic Functions”, we used a nested “Hierarchical” diagram to accomplish this since all we were working with were a series of nested “IF” statements (“If this, do this, if not do that”). Although we could take this same approach to detail the logic this time, it wouldn’t really help us too much since there’s only one “IF” in our problem.

Now that we’ve looked at how to trace down the logic of an “OR” function and that of an “AND” function, we’re going to merge the two in order to be able to test for multiple conditions together.

Last night, I wanted to sum a calculated column with groups on the page with the groups closed. SharePoint does not do this OOB and my previous attempt at a solution needed data rendered (groups expanded).

To accomplish this, I did two things to my OWS API. First, I added a getType method that [...]

To model this approach, we looked at using common logic modeling tools like “Truth Tables” and “Hierarchical Flowcharts” to first build out the path the logic will take, then transform that logic into a complete formula we can simply paste into a SharePoint calculated column.