Hi everyone,
This is Amber from the SharePoint Designer Data team. This post walks you through using calculated fields in Windows SharePoint Services (WSS) to show items from the Current month. Lots of posts cover the use of calculated fields, but none specifically address the use of Formulas for dates in a calculated field. At the end of this blog you will have a good understanding about calculated fields with some helpful links.
In this example we have a Service Request List and the end goal is to display all the service requests placed in the current month. The way we will do this is by creating a calculated field that returns something that is filterable.
Step 1:
First, we will create a “Custom list” called “Service Request List” with the following fields:
· Service Request Title (String) - Comes by default
· Request Date (Date)
· Resolution (Date)
· Today (Single line of text)
Now go ahead and add few items in the list with Request Dates with different months.
Step 2:
Now, let’s create a calculated field to evaluate if the initial request date belongs to the current month or not. If it does then it would return Yes, else No.
1. Now click back on list settings option and create a new column
2. Specify a column name (I used OpenedThismonth)
3. For type select “Calculated based on other columns”
4. For the formula I used
=IF(MONTH([Today])=MONTH([Request Date]),"Yes","No")
*Note: While specifying a formula for the Calculated fields you can’t use [Today] expression. To work around this problem we created a Today field in step 1 and then deleted it in step 3 (listed below).
5. Now for Data type returned for this formula select “Single line of text”
6. You can also uncheck “Add to default view” to make sure that this calculated field is not displayed in the new view that you are creating
7. Click OK
Now you have a calculated field “OpenedThisMonth” which specifies weather a Service Request was created in the current month.
Step 3:
In this step lets use the filterable field that we created in step 2 to modify our existing view.
NOTE: Before we create this filter we will first delete the "Today" field so that our calculated field created in step 2 works.
To do so here are the steps:
- Go to the list settings page
- In the columns section click on the "Today" column
- Click Delete, and then OK
- At this point we should have a view without the "Today" column
1. Now click on Modify View to go to the Edit View page
2. Scroll down to the filter section
3. For the columns value select the column you created in step 2 (OpenedThisMonth)
4. In the comparison dropdown select “is equal to”
5. In the value text box enter “Yes”
6. Click OK
At this point you should have a view showing all Service Requests placed in the current month.
End Result:
Some useful links:
Hope this helps,
Amber
