Overview

In continuance of the series on the “Date and Time” set of functions that I wrote about back at the end of May (that darn “Work” always getting in the way), I’d like to discuss the actual “DATE” function itself in order to help address some of the more frequent questions regarding how to deal with dates in SharePoint.

The Function

DATE: this returns the “sequential” serial number that represents a specific date.

It’s syntax is:
=DATE(year,month,day)

“year” represents a value between 0 and 9999
“month” represents a value between 1 and 12
“day” represents a value between 1 and 31

Given the above, this would mean that its usage could be something as simple as:

=DATE(1971,9,15)

This will return the serial number of “26,191” (as a side-note: can anyone guess the significance of the date I’m using for this example?).
In SharePoint, all dates are stored as serial numbers in order to make it easier (subjective term) to use them in calculations.  The reason it winds up being easier is because it places the date value in a standard format that separates it from any formatting rules applied by localization (U.S. date format vs. European vs. Australian, etc.). 

In a way, you could think of it as the perfect example of separating “Presentation” from “Content” – the data is in a truly universal format that can be used in any calculation needed, then “formatted” back into a friendly display that adheres to any needed localization rules.

Tools of the Trade

Looking at the serial number itself, we can validate its accuracy by performing some simple math (another subjective term) based on the date that SharePoint uses as the starting point (December 31, 1899), or if you’re like me and hate math (that’s why I work with computers – it has a calculator on it), you can find the number of days between two dates by using a service on “time and date.com” (http://www.timeanddate.com/date/duration.html).

Simply enter in our start date of “December 31, 1899” (the SharePoint date serial number of “1”), and our end date of “September 15, 1971” then click “Calculate duration” (make sure and select the “include end date in calculation” or else we’ll be off by one).

Of course it’s not a SharePoint resource itself, but it does validate that our result was accurate, and I always wind up using various tools online to assist me in my work, and this is a great one for working with dates to make sure we’re getting the results we should.

One thing I do want to point out though in how SharePoint actually deals with dates is that if you’re working with a year value that is less than 1899 (anywhere in between 0 and 1899), the year value will actually be itself plus 1900.  So if the year value is 12, it will actually be 1912 since the first serial number that SharePoint will be able to use is based on 1899 (1899 will equate to a serial number of 1, so 12 will become 1912, which will equate to a serial number of 13).  Although this can appear confusing, as long as you remember that the first year SharePoint can use as its starting point will always be 1899 and that you will have to add any year before it to 1900 (the year + 1900), things will turn out fine.

The next step is to move this into SharePoint to make sure that what we have on paper (and on our newly discovered helper utility on the web) will produce the same results.

Dessie Lunsford
Points-of-Sharing