SharePoint User Group UK

Hello again.

Here's a problem that's really bugging me:

I have a list of news articles which each have a date of article in a column called 'Newsdate'
I need to use a CQWP to display all articles that are a week or less old.
Due to SharePoint not allowing calculations to be made in a filter on a date field in the CQWP (any ideas why not by the way?) I have created three addition columns:

• Today (which is a calculated field with the formula =Today)
• Today-7 (which is a calculated field with the formula =Today-7) (You cannot just use this formula straight away - you need a column called 'Today' first for some reason - argh!)
• Thisweek (which is a calculated field with the formula =IF([Today-7]<=Newsdate,1,0)

This works absolutely fine and I have an extra column in my pages library list with either a '0' (more than a week old) or a '1' (less than a week old) populated the column.

My problem is this. When I add a Content Query Web Part and try to filter (Additional Filters) on "show items when" Thisweek (is equal to) 1, I get absolutely no results returned.

It is really annoying me - can anyone see where I am going wrong?

Hope you can help!

Joe

You may have to create a console application to update each list item. Then schedule it to run every day. Then it's possible to make "Today" field dynamic and display data as expected.

 Hava a look on the following post for more details.

http://www.sharepointblogs.com/dez/archive/2008/07/31/dynamically-updating-a-sharepoint-calculated-column-containing-a-quot-today-quot-reference.aspx

Hi Joe,

I think we can't use dates in queries as 2/3/08 gets misunderstood as 2 divided by 3 divided by 8. (This is the way it works in Excel and SharePoint shares alot of the same logic).

Your formula contains one mistakes "[Today-7]" should be "[Today]-7".

Rob

Perhaps you would be better to create a calcualted column based on the Modified Date of the item.  So a create a new column called Expiry, set the type to Date/Time and set that to date only.

In the formula box type:

[Modified]+7

This will give you the expiry date of the content.  For example if the Modified Date is 15/10/08 then the expiry date will be 22/10/08.

Apply a filter of

[Expiry] Greater than or Equal [Today]

If you want to not display the content on the seventh day just make the filter Greater Than.

Also if you want Posts to expired based on Creation date instead of the Modified Date, then simply use the Created field.

Ofc the reply 2 above mine is pretty much the same..