Tuesday, July 17, 2007

Filter by year

I got a question by a customer who wants to filter listitems on a specific year (eg. show me all the items from the year 2007). It's not that difficult to achieve since you only have to build a calculated column and use that column to get the year from a date column (like the created or modified date). So what I did was go to Excel and use the formula which I thought was the correct one and that was : =Year([DateColumn])
So I ended up with the following column..

As you can see.. there is a decimal breaking up the number (although the columntype is 'single text column'). Getting back to Excel, I checked the format of the cell and there I found the following:

If the 'Use 1000 separator' was checked, the year was also broken in Excel. So I found the cause of my problem! Now.. where do you uncheck the separator in SharePoint eh?
Well .. I couldn't find it, but I found another solution while I was googling and that was:  =TEXT([DateColumn],"yyyy") (http://support.microsoft.com/kb/887922/en-me)
And voila check out the following screenshot with the desired outcome :)

 Btw if you receive errors when you  typed in the formula, it could be due to the separator character. For example if your regional settings are Dutch, the formula looks like =TEXT(Created;"yyyy"). Mind the ';' character instead of the ',' character (see also the KB article that I mentioned)


Technorati tags: , ,


Wade Draper said...

Great job. Accomplishes exactly what I need.

Ryan said...

This rocks, thanks so much.

Bob said...

Odd Behavior:

I have set this up as so that the "year" that a document is saved in a library it shows it in the view. The odd thing is that when I save a new document the year is set as 1899. If I just go into the column "year" and close and go back to the library "all" view, the date is then the correct year (2009). Any ideas? WSS 3.0 64 bit / Office 2007 document opened by clicking new via WSS

Robin Meuré said...

Hi Bob,

depends on what you use to create the year from. When 1899 is being displayed it means that when calculating the coloumn the value was null and in this case 1/1/1899 (lowest DateTime possible)..

Ryan said...

Related - If you want to setup a filter showing only the current calendar month/week/year then this post may help


Isha said...

I know it's bad form to post to ancient blogs but I'm flailing.

I'm getting the same as Bob.. but with mine it's 1905 instead of 1899. I do =TEXT(YEAR(Modified),"YYYY")

I know that modified is not empty, and holds the right year so the value can't be null.

Why is working with dates so cumbersome in SharePoint??

Isha said...

So it goes!... As soon as i posted I flashed that it's the 'YEAR' part of the formula.

For some reason, including YEAR gives you 1905.

=TEXT([Modified],"YYYY") gives you the correct year.