If you look at the progression we took on these three “versions”, this last one references the “Serial Number” column, which references each of the three date columns (“Year”, “Month”, and “Day”), which each run a function to grab a specific piece of our original date field. Since we’ve converted the original date into a SharePoint serial number, and broken the original date down into its individual pieces, this approach makes the most sense since the work is already being performed – we’re just going to repurpose it when needed.

Once updated, the values displayed on the list should be the same since we’re using the same formula – the only difference is that we’re performing the calculations in separate columns instead of within the actual “DATE” function itself (the logic is still the same though as to what being processed because each referenced column will perform its function then pass its value back to its calling column – the “Serial Number” column).

Aggregating data with a DVWP and a Linked Data Source can really save you lots of time and make your SharePoint lists do some great online reporting.

One of the more frustrating (albeit understandable) elements of working with calculated columns is the restrictions on what column types can be referenced in your formulas. What it all boils down to is that you can only reference fields that have a return type of “Text” and these must be in the form of a single value (i.e. “Single line of text”, “Number”, “date/Time”, etc.). What you can’t reference is things that can have more than one value or more than one line (“Lookups”, “Multiple Select” choice fields, “Multiple lines of text”, etc.)…or can you?

Hopefully all of this wasn’t too confusing – it’s a hard formula to decipher, but as with all formulas, the best approach is to break it into chunks, determine what each chunk does independently, then merge them all back together and run through the entire thing with a series of test data.

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)