Date Functions in Calculated Fields

Date Functions in Calculated Fields

  • Comments 32

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.

clip_image002

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

clip_image004

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.

clip_image006

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”

clip_image008

6. Click OK

At this point you should have a view showing all Service Requests placed in the current month.

End Result:

clip_image010

Some useful links:

Hope this helps,
Amber

  • Just tried to create Today’s Date column and turn my calendar one day forward. Today’s Date value left the same, so it isn't pointing to real today. What are you doing here is the same as to use default Created column value because Calculated value is calculated just once when the item is created. I think there is a simpler solution to this. You could create Calculated colum like this NextMonth=DATE(YEAR([Created]), MONTH([Created])+1, 1) and then in your view filter use condition NextMonth > [Today] what means that you need to shows those items where today is still less then the first day of the next month (from item's Created column value).

  • I wonder if Amber (author of this post) even bothered to test this "recipe" before posting it to an official SPD blog.

    Just as Mndaugas said, this doesn't work. [Today] is only calculated on item creation and edit, not every time list item is accessed.

    Now, please, whoever posts to this blog - check your solutions before posting them. Or at least google them up. Filtering by [Today] and why it doesn't work has been discussed a hell lot of times already.

  • The solution posted is not working. I fully agree with wellick's comment!

  • As previously mentioned filtering using [today] one a default view will not work.  If you want to filter using [today] you will need to use SPD and create a data view you can then use xslt in the filter and do it.

    Unfortunatly the version of xslt that ships with Sharepoint 2007 is very limited and can be a pain so it is often better to mix calculated columns and xslt instead of doing it all in xslt.

  • Then Wellick, you should be able to solve the problem.

    This is a good tip for people starting out with calculated values etc, the meaning isn't to provide full-scale solutions. If this doesn't suit your needs, you are free to customize this solution and do whatever changes you'd like.

    Instead of picking on people who provide FREE support and assistance, you should perhaps show some appreciation.

    Cheers

  • Amber- you suc!!  Seriously, check the web, this solution doesn't address the core problem of being unable to create a [Today] calculated column that dynamically updates the current date on a daily basis.  Maybe Amber should talk to Bill G. about why MS bugs need to be discovered & flushed out by his customers rather than his programmers??

    AC

  • Thanks for the feedback guys. I have updated the post to work around the problem that you guys are hitting.

    Hope this helps.

    Thanks,

    Amber

  • Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

  • Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

  • Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

  • Did you guys fix the problem? I'm still trying but the propsed solution doesn't work...

    @Amber: why are you screen shots different from your writing??? E.G. you say in step 1 'Taday' but on your screen shot there's 'Today's date'

    That's mess...

  • In my opinion the “Today” column is useless, I have explained it here:

    http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/

  • Direkter Download: SPPD-102-2008-08-14 Aktuell Feedback Usergruppen ISPA International SharePoint Professionals

  • Direkter Download: SPPD-102-2008-08-14 Aktuell Feedback Usergruppen ISPA International SharePoint Professionals

  • Pues nada, dicen que las tradiciones están para mantenerlas en el tiempo y eso es lo que espero hacer

Page 1 of 3 (32 items) 123
Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post