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:
SharePoint,
MOSS,
WSS
