Moving it all Back

Now that we’ve successfully ran the logic to find out what the serial number is for a given date, and used individual functions to parse out the individual pieces of what’s needed to get the serial number itself, we’re going to look at the process required to take the serial number and convert it back into an actual date.

The process itself is actually rather simple, but I want to go over three different approaches that help to illustrate the logic taking place in order to have a better understanding of why the serial number is important in the first place.

To start, create a new column called “Converted Date”.  Make it a “Calculated” type with the rest as default.

For the formula, we’ll use three different versions.

Version #1 (overly complicated approach):

Since we’ve used the three date functions in separate columns for “Year”, “Month” and “Day”, we’ll duplicate them into this formula, wrap them in a “DATE” function to fill its 3 required pieces, and then wrap it all into a “TEXT” function with a text formatting style to have it display as a readable date.

Formula:

=TEXT(DATE(YEAR([Serial Number]),MONTH([Serial Number]),DAY([Serial Number])),"MM/DD/YYYY")

Back on our list we should see the results of our “Converted Date” column displaying the correct date.

Version #2 (much cleaner approach):

Again, since we’re already handling the parsing of the year, month, and day in other columns that each has a function performing the needed calculations, why should we duplicate it again in our conversion formula?  Instead, we can simply reference those columns as needed and let them do the work for us.  So, in our “Converted Date” column, change its formula to:

=TEXT(DATE([Year],[Month],[Day]),"MM/DD/YYYY")

After saving, go back to the list to see the results:

Same results, but easier to modify the formula if needed since it’s not all cluttered up in a single nested formula.

Version #3 (one word – “Simple”):

One of the nicest things about working with date objects in SharePoint is that it doesn’t matter the format the date is in when using it in a formula.  Functions such as “YEAR”, “MONTH”, and “DAY” always return values that will be in a “Gregorian” format, and realistically, most functions will automatically convert dates into serial numbers anyway so to make it easiest on ourselves, we’ll just use the serial number to convert back to a “readable” date format instead of going through unnecessary hoops just to try and format the value.

In our “Converted Date” column, modify its formula to be:

=TEXT([Serial Number],"MM/DD/YYYY")

Checking our list will once again yield the exact same result because we’re still performing the exact same process as the two times before, just without needlessly duplicating the efforts of our existing columns:

Note – you’ll notice that in each of these versions of converting the date back into something more readable that the month is being displayed in a 2-digit format instead on the original 1-digit format.  This is because in my “TEXT” function I’m forcing it to display in 2-digits regardless of whether it’s actually 1 or 2.  In some cases this may actually be fine in its usage, but I’d most likely wrap the function into a “CHOOSE” function or simple nested “IF” function to determine if the month value was less than or equal to 9 in order to remove the leading zero – but that’s just me (you may be fine with the results as is).

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.

Summary

One of the reason I’ve really enjoyed working with these formulas is because it’s almost like having your own little work crew there to do your bidding.  Yes, you have to tell them each what they need to do (vocational training I suppose) and make sure they can actually do it without any hazards (or else “SharePoint-OSHA” will step in and bark at you), but the nice thing is there’s not too much overhead you have to be concerned with when creating the functionality needed.  You can experiment with different approaches, refine your tactics as you discover more efficient processes, and create many “helper” functions (individual non-displayed calculated columns that each performs a specific function) that can each sit separate until needed (our “Year”, “Month”, and “Day” columns in the examples).

Oh, and by the way…the date I was using in the examples is my birthday (virtual beer to those that guessed it earlier).

Till next time…
– Dessie

Dessie Lunsford
Points-of-Sharing