Showing This Month’s Documents in a SharePoint List

I’ve frequently been asked if it is possible to display only the current month’s documents in a SharePoint list.  There is no obvious way to do this, as the filtering mechanism in a view will not allow you to specify a formula as its criteria.

The trick is to create a calculated column on the list.  I like to create a Site Column for this so that it is available to me anywhere in the Site Collection that it might turn out to be useful.

So, create a column (either on the list or as a Site Column), choose “Calculated (calculation based on other columns)” as the type of column, and make the data type “Yes/No”. (This is simply a Boolean column, but Yes and No are the values displayed rather than TRUE or FALSE.)  Now all you need is a formula that evaluates to TRUE if your conditions are met, and you’re off and running.

Here’s an example.  I created a column called ThisFiscalPeriod, with the formula:

=AND(TEXT(YEAR(Today),”####”)=[Fiscal Year],(TEXT(29*MONTH(Today),”mmmm”)=[Fiscal Month]))

I had already created columns called Fiscal Year and Fiscal Month that were part of the Content Type that I was using in the list.  Whenever the current year and month matched the Fiscal Year and Fiscal Month, the formula evaluates to TRUE, and the column will display or can be tested to see if it is “Yes”.  (See my earlier post on how to use the Today value in a formula.)

If you wanted to show all of the items that had been modified in the current month, then the formula would look like this:

=AND(TEXT(YEAR(Today),”####”)=TEXT(YEAR([Modified]),”####”),(TEXT(29*MONTH(Today),”mmmm”)=TEXT(29*MONTH([Modified]),”mmmm”)))

Note the neat trick in this part of the formula: TEXT(29*MONTH(Today),”mmmm”).  MONTH(Today) returns a number from 1 to 12 which indicates the current month (1=January, 2=February, etc.).  Multiplying that number by 29 gives a serial day number that *has* to be in the right month.  The following table shows how it works:

  

Date	Month	Serial Day	Text Month
1-Jan	1	29	January
1-Feb	2	58	February
1-Mar	3	87	March
1-Apr	4	116	April
1-May	5	145	May
1-Jun	6	174	June
1-Jul	7	203	July
1-Aug	8	232	August
1-Sep	9	261	September
1-Oct	10	290	October
1-Nov	11	319	November
1-Dec	12	348	December