18 January 2011

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

No comments:

Post a Comment

Comments are moderated only for the purpose of keeping pesky spammers at bay.

SharePoint Remote Event Receivers are DEAD!!!

 Well, the time has finally come.  It was evident when Microsoft started pushing everyone to WebHooks, but this FAQ and related announcement...