Tuesday, May 6, 2014

SharePoint 2010 Filter View by Quarter (Date)

This is an example of how you could create a view, with the help of calculated columns, to filter items in a SharePoint list by the quarter.

First, create 2 calculated columns of type Date and Time that will be the quarter start and quarter end of your preferred date.
e.g.
the date today: 5/7/2014 (Q2)
quarter start will be (1/4/2014) while quarter end will be 30/6/2014



I will take Created date as an example.

Formula for QuarterStart:
=IF(MONTH(Created)<=3,DATE(YEAR(Created),1,1),IF(MONTH(Created)<=6,DATE(YEAR(Created),4,1),IF(MONTH(Created)<=9,DATE(YEAR(Created),7,1),IF(MONTH(Created)<=12,DATE(YEAR(Created),10,1)))))


Formula for QuarterEnd:
=IF(MONTH(Created)<=3,DATE(YEAR(Created),3,31),IF(MONTH(Created)<=6,DATE(YEAR(Created),6,30),IF(MONTH(Created)<=9,DATE(YEAR(Created),9,30),IF(MONTH(Created)<=12,DATE(YEAR(Created),12,31)))))

Now create a new view and add a filter based on those fields:



No comments:

Related Posts Plugin for WordPress, Blogger...