In the next part of this series, we’ll be wrapping things up with a quick revisit to our (now more understandable) formula, and summary.

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.

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.

The “AND” function is similar to the “OR” function in that it will look at up to 30 different values for comparison, but it’s difference is that in order for the final “Result” to be “TRUE”, each and every condition must be true in the entire check or the end result will always be “FALSE”.

Browsing through some SharePoint stuff this morning I came across this JavaScript add-in for embedding videos into SharePoint list items.

As I was putting together the Calculated Column module for the Excel and SharePoint live online workshop, I pinged Dessie Lunsford to see if he had any material I could use to show people how to generate formulas in Excel that could be transformed into a formula for a calculated column in SharePoint.