In his continuing series, Taming the Elusive "Calculated Column" – Date and Time, Dessie explores using the date formula within separate columns.

The List

For this, I’m using a custom list that I’ve added a new calculated column to called “Serial Number”.  So, once you have your list, create a new calculated column, and give it the following formula:

=DATE(1971,9,15)

Note that although, I have my column set with a return type of “Number”, you could leave it as a “Single Line of Text” – the results in this case will be the same.
Once the column is created, add a new item to the list to see the results.

The value displayed should be the same result we got from the web tool so we now know that the serial number is correct.

What we need to look at now is how to work with a date that isn’t “hard-coded” into the formula so we can start to see something more of a real-world example.

Moving back to our list, let’s start by deleting our test item and “Serial Number” column so we can start clean.

Next, create a new column called “The Date” and make it a “Date and Time” type.  Leave the rest as default then click “OK” to add it to the list.

Create another new column called “Serial Number”, make it a “Calculated” type, and then enter in the following formula:

=DATE(YEAR([The Date]),MONTH([The Date]),DAY([The Date])

Couple of things to note here:

First, this time I’m leaving the return type as the default “Single line of text” – for this example the results will be the same whether I choose a number as the return type or text (this isn’t always the case, but for this example it is).

Second, remember that the syntax for the “DATE” function is expecting 3 things: “Year”, “Month”, and “Day”.  Because of this we have to break apart the entire date object into its associated parts by running separate functions to grab the year, month, and day from it.  Since we need these values for the “DATE” function, let’s go ahead and create 3 more columns so we can see these other functions in action.

Create 3 new columns named “Year”, “Month”, and “Day”.  Make each of them a “Calculated” type, and enter in the following formulas into them (respectively):

=YEAR([The Date])
=MONTH([The Date])
=DAY([The Date])

Once all columns have been created, go back to your list and create another test item (I’m going to use the same date I had earlier and manually type it in so I don’t have to click through all the months to get back to 1971).

What we now see is that our serial number is again reflecting the correct value and that our individual date columns (“Year”, “Month”, and “Day”) are displaying just the piece each needs based on their formulas.

Since we’re using the exact same formulas for each of these columns in the “Serial Number” column (for each of the parts it needs in order to work), the values displayed in each of the date columns is the literal value being passed into the “DATE” function in the “Serial Column” (which is exactly how we had it hard-coded in the first place).  So again, we’re proving that the results are accurate.

Version Next

To improve this some since we already have the three parts of the “DATE” formula being calculated within their own columns, we can simply modify the formula in the “Serial Number” column to use those columns in its formula as such:

Original formula:
=DATE(YEAR([The Date]),MONTH([The Date]),DAY([The Date]))

Change to:
=DATE([Year],[Month],[Day])

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).

Dessie Lunsford
Points-of-Sharing