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

Written by Cornelius J. van Dyk on . Posted in Blog

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




image

Tags: ,

Trackback from your site.

Cornelius J. van Dyk

Born and raised in South Africa during the 70's I got my start in computers when a game on my Sinclair ZX Spectrum crashed, revealing it's BASIC source code. The ZX had a whopping 48K of memory which was considered to be a lot in the Commodore Vic20 era, but more importantly, it had BASIC built into the soft touch keyboard. Teaching myself to program, I coded my first commercial program at age 15.

After graduating high school at 17, I joined the South African Air Force, graduating the Academy and becoming a Pilot with the rank of First Lieutenant by age 20. After serving my country for six years, I made my way back into computer software.

Continuing my education, I graduated Suma Cum Laude from the Computer Training Institute before joining First National Bank where my work won the Smithsonian Award for Technological Innovation in the field of Banking and Insurance. Soon I met Will Coleman from Amdahl SA, who introduced me to a little known programming language named Huron/ObjectStar. As fate would have it, this unknown language and Y2K brought me to the USA in 1998.

I got involved with SharePoint after playing around with the Beta for SharePoint Portal Server 2003. Leaving my career at Rexnord to become a consultant in 2004, I was first awarded the Microsoft Most Valuable Professional Award for SharePoint in 2005, becoming only the 9th MVP for WSS at the time. I fulfilled a life long dream by pledging allegiance to the Flag as a US citizen in 2006. I met the love of my life and became a private consultant in 2008. I was honored to receive my ninth MVP award for SharePoint Server in 2013.

Leave a comment

You must be logged in to post a comment.