Using the YEAR from a Date column in a calculated column in SharePoint

I got this question from someone on the forums and thought it might be worth reposting to my blog for later reference as well.

The question:

Hello – I have an out-of-the box date column titled, “Departure Date” which renders values in the format, ‘DD/MM/YYYY’. I want to be able to filter by Year only so I believe the easiest way to accomplish this is to create a calculated column titled, Year and populate that column with the YYYY values from the Departure Date column. I tried using several formulas but so far have been unsuccessful. I’m currently using this formula, “=TEXT(YEAR([Departure Date]),”YYYY”)” for the calculation value and the value comes up as “1905”…? Help!

Now this one made me scratch my head for a minute and then I fired up my trusty “Calculated Column Debugger”… Excel!!!

USEFUL TIP:  ALMOST ANY FORMULA YOU CAN PUT TOGETHER IN EXCEL, YOU CAN COPY TO A SHAREPOINT CALCULATED COLUMN AND IT SHOULD WORK JUST FINE.

I copied the formula over to an Excel cell and put a date in another cell to reference and sure enough… I got 1905 too!  Then just looking at the formatting options for the TEXT method and the YEAR method, I was able to deduce the answer thus…

The answer:

The problem is that you’re doubling up on methods by passing the result from YEAR to the TEXT method. Either of the following should work just fine:

• =TEXT([Departure Date], “yyyy”)

or

• =YEAR([Departure Date])

Because the TEXT method is expecting a DATE value to be passed, but the YEAR method is returning a TEXT value, it confuses the TEXT method which doesn’t know how to interpret the value and thus produces the weird result you saw.

Cheers
C