Creating a Current Year View in SharePoint

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, or even current year. I created a blog post for the current week in a previous post, as well as a post for the current month. Today I am sharing the formula for creating the current year view.

Just like a week view, we need to create a YearStart and a YearEnd field to gather the beginning and end of the month.

Both of these columns are calculated fields.

The formula for the Start of the Year is

=DATE(YEAR([Created]),1,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 first month (1)
  • Then give me the first day (1)

The formula for the End of the Year is

=DATE(YEAR([Created]),12,31)

Which tells SharePoint to do the following:

  • Give me the YEAR from the Created field
  • Then give me the 12th Month
  • Then give me the 31st day

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: YearStart is less than or equal to [Today]

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

Advertisements

Creating a Current Month View on SharePoint

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]

Creating a view for specific users

When you are creating solutions for different people, you often want to limit the information shown to only the user to whom the information applies. Once example of this is My Tasks, for which a “My Tasks” view already exists out of the box. But what if you need to create that type of view in a different list?

Creating a view specific to the user who is looking at it, is actually surprisingly easy to do by using List Views.

The only thing you need to be sure about is that your list contains a “People or Group” column type that you can leverage.

To access the view options, click on the name of the list, and then from the LIST TAB at the top of the screen, select Create View if you want to create a new view, or Modify View if you want to change the current view.

Scroll down to the FILTER options for your view, and choose the appropriate “People or Group” field for your list. In some cases it’s Assigned To, in others it’s Created By, and in your own list it could be whatever you named the field.

Once you’ve chosen your field, select “is equal to” from the drop-down, and type [Me] in the value box.

Using [Me] takes the information and only shows the applicable information for which that person’s name exists in the chosen field. Using this option is a great way to hide other people’s information from the specific user.

Creating a Monday through Sunday View in SharePoint

In my previous post I explained how to easily create a current week’s view. But, what if you need the current week to show from Monday through Sunday?

In order to create a current week’s view with a specific day range, you need two additional fields in your list. For simplicity’s sake, we will call them WeekStart and WeekEnd. Both of these fields are calculated fields.

The WeekStart field has the following calculation: =[Start Date] – WEEKDAY([Start Date],2) + 1. You cannot copy and paste this formula directly into the field – you have to build it from scratch. You do so by typing the equal sign (=), then double-clicking on the date field you want it to calculate (e.g. Start Date), then type – WEEKDAY( and then double-click the date field again. You can then copy the ,2) + 1 portion into the calculation.

The WeekEnd field has the following calculation: =[WeekStart] + 6 which again, you will have to build from scratch by typing the equal sign (=), then double-clicking on the WeekStart field, then type + 6 to complete the formula.

You will notice that any existing data is automatically updated with these two calculated fields.

The next step is to create the view that only shows the current week.

To access the view options, click on the name of the list, and then from the LIST TAB at the top of the screen, select Create View if you want to create a new view, or Modify View if you want to change the current view.

Scroll down to the FILTER options for your view, and choose the WeekStart field and select “is less than or equal to” from the drop-down, and type [Today] in the value box. Next, choose the WeekEnd field and select “is greater than or equal to” from the drop-down, and type [Today] in the value box.

That will ensure that your list will only show you the data for the time between WeekStart and WeekEnd.

Creating a Current Week view in SharePoint

If you want to create a view with just the upcoming week’s data, one way to accomplish this, is to add a filter to a list view.

To access the view options, click on the name of the list, and then from the LIST TAB at the top of the screen, select Create View if you want to create a new view, or Modify View if you want to change the current view.

Scroll down to the FILTER options for your view, and choose the field you need to filter (such as created by, start date, due date, etc.) and select “is greater than or equal to” from the drop-down, and type [Today] in the value box, and then choose the field you need again, and select “is less than or equal to” from the drop-down and type [Today] + 6 in the value box.

That will create a view showing you everything that’s happening between today and the same day next week.