Creating a Current Month View on SharePoint

Office 365 / SharePoint Blog

SharePoint can handle some date calculations on the fly. One of those is to use [Today] which lets you create upcoming views or past views based specifically on today’s date. Such as, use [Today]+7 to generate the next 7 days. Or use [Today]-21 to give me a date from 3 weeks ago.

SharePoint is not so good at handling current week or current month. I created a blog post for the current week in a previous post. Today I am sharing the formula for creating the current month view.

Just like a week view, we need to create a MonthStart and a MonthEnd column to gather the beginning and end of the month.

Both of these columns are calculated fields.

The formula for the Start of the Month is

=DATE(YEAR([Created]),MONTH([Created]),1)

It’s essentially an EXCEL formula that works in SharePoint. To break apart this formula, let’s look at each part:

The Date Formula gathers the YEAR info, then the MONTH info, and then the DAY info.

The above formula then essentially says:

  • Give me the YEAR from the Created field
  • Then give me the MONTH from the Created field
  • Then give me the first DAY of this month (that’s where the 1 comes in)

The formula for the End of the Month is

=DATE(YEAR([Created]),MONTH([Created])+1,1)-1

Which tells SharePoint to do the following:

  • Give me the YEAR from the Created field
  • Then give me the MONTH from the Created field, and add 1 to it (so make it next month)
  • Then give me the first DAY of the month (so it’s now the first day of next month)
  • Then subtract 1 day from that (which gives you the last day of this month)

Applying these columns in a Filter

The next step is to use these dates in a filter so it will show you the current month.

We do that through the use of VIEWS

In the filter section of the view, use the following references:

Column: MonthStart is less than or equal to [Today]

Column: MonthEnd is greater than or equal to [Today]

Advertisements

1 thought on “Creating a Current Month View on SharePoint”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s