Date Functions in Calculated Fields

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:

• Introduction to data calculations
• MSDN reference to calculated field formulas
• Some examples of common formulas

Hope this helps,
Amber

Comments

• Anonymous
  August 03, 2008
  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).

• Anonymous
  August 04, 2008
  The comment has been removed

• Anonymous
  August 04, 2008
  The solution posted is not working. I fully agree with wellick's comment!

• Anonymous
  August 05, 2008
  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.

• Anonymous
  August 06, 2008
  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

• Anonymous
  August 08, 2008
  The comment has been removed

• Anonymous
  August 08, 2008
  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

• Anonymous
  August 11, 2008
  Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

• Anonymous
  August 11, 2008
  Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

• Anonymous
  August 12, 2008
  Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

• Anonymous
  August 13, 2008
  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...

• Anonymous
  August 14, 2008
  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/

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

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

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

• Anonymous
  August 21, 2008
  All the workarounds fail in one or the other way. So, Microsoft have to add 'today' option for calculation. That would be the only solution.

• Anonymous
  October 01, 2008
  Adding the [Today] column worked great for what I was trying to do.  I am using it to track the progress of projects, and I simply want to know if a project is On Schedule or Late for a report I need for my boss.  The work around worked fine for this purpose.

• Anonymous
  December 03, 2008
  You need to create a text only column and Title it "Today."  Create your calculated column and reference the today.  Once the Today column is deleted Sarepoint treats it as a "Today" variable.

• Anonymous
  December 09, 2008
  Worked great for me.  Thanks Amber:)

• Anonymous
  December 26, 2008
  The comment has been removed

• Anonymous
  September 14, 2009
  This worked great Amber thanks! I don't know what all these other guys are talking about. if they follow the instructions it works...

• Anonymous
  October 26, 2009
  The solution works perfect for me, I had Have to change the separator comma by the semicolom because in my sharepoint have installed Spanish languaje pack and it uses the comma as decimal comma. The formula that works in my site is: =IF(MONTH([Today])=MONTH([Request Date]);"Yes";"No") Thank you for your post.

• Anonymous
  October 29, 2009
  If you have Problems to Query the Calculated Field read my Blog: http://www.schonebeck.net/?p=5

• Anonymous
  November 01, 2009
  This technique DOES work however has the limitation that the "Yes/No" value calculated only at item creation time or when the "today" column is added and subsequently deleted.   You will find that as the current crosses into a new month, the "OpenedThisMonth" column will not be automatically recalculated. To make this a viable solution, someone with list admin privileges would need to recreate the fake today column and delete it again manually each month.

• Anonymous
  November 26, 2009
  I don't get why people keep saying "the Today trick works - it just doesn't update"? Doesn't that make it about as useful as a parachute that opens... 5 foot above the ground? ;) I've talked the today trick here - http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/ @Brent - the abve also includes some links to workarounds by Dessie such as a batch job  that can automatically add/remove the column to keep it up to date. I've also blogged about an alternative method for filtering items from the current/previous calendar month http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/

• Anonymous
  February 04, 2010
  I would like to assign icons to the results.  How do I do that?

• Anonymous
  March 26, 2010
  This worked great!! Now I can impress all my friends :-)Thanks for the post.

• Anonymous
  April 09, 2010
  Hey this works perfectly fine ... Great great tip ... Thanks a Lot :)... people please try before commenting on something. i don have words to thank... I have been looking for this sometime

• Anonymous
  June 03, 2010
  Hi This is nice trick, but it has a problem. Many times Today or Modified Date is used in calculated column, shows as 30 Dec 1899. This is known problem and many has faced this issue. Still no one has it's solution. Initially it works perfect but when sites are moved to any other server for production or new template is created with the existing sites. it will be visible. If any one has any solution for dates in calculated column (shows as 30 Dec 1899), please post. Thanks, Chirantan

• Anonymous
  August 16, 2010
  This works for me, though I had to further customize the formula to include the year. With the current formula given by Amber, the view will show all items for the current month but the year is never filtered. So for the month of August for example, you'll get items from August 2010, August 2009, August 2008, etc. This is my formula that worked to also check the year before checking the month. =IF(YEAR(Today)=YEAR([Article Date]),IF(MONTH(Today)=MONTH([Article Date]),"YES","NO"), "NO")

• Anonymous
  January 09, 2012
  its not working ....before posting this plz check and confirm the post.....=IF(MONTH([Title])=MONTH([Request Date]),"Yes","No")  its throwing error....."The formula refers to a column that does not exist.  Check the formula for spelling mistakes or change the non-existing column to an existing column"

• Anonymous
  August 28, 2012
  Hello, I am trying to display data each week, but Wednesday - Wednesday.  Right now, the only formula I have is Monday - Sunday (Weekly), but wish to do it Wednesday - Wednesday.  Here is the formula I am using: Week Start: =[Due Date]-(WEEKDAY([Due Date],2)-1) Week End: =[Due Date]+(7-WEEKDAY([Due Date],2)) And I set my views to Week Start <= [Today] and Week End >= [Today]. When the user enters a date in between those times, it displays by the week, however I need it to display by wed-wed.  I tried playing with the numbers and cannot seem to figure it out.  Any ideas? Thanks