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

1 Comments

Have a thought or opinion?