How can we create and calculate a ([Due Date]- [Today])/7 column in SP 2010?

Question

How can we calculate ([Due Date]- [Today])/7 column that updates daily as value of Today changes and lets us calculate weeks left for due date ? Could create ([Due Date]- Today() )/7 and it worked fine but it value of Today() in this is not dyanmic and changes only when we edit an existing entry or create a new one .

Any ideas as how to achieve display of weeks left for due date dynamically ?

Answer 1

Did you google "dynamic value of today sharepoint" ? You can see that this topic has been discussed again and again, in MOSS and SP2010.

There is a workflow with loop "solution" : https://sites.google.com/site/sharepointhill/tutorials/mssp/sp-dynamic-today-date

Or a XSLT "Solution" : https://sharepointapplied.wordpress.com/2009/04/23/useless-calculated-column-today-trick-xsl-today-to-the-rescue/

Or you may want to add some custom code or timer jobs that update each element of the targeted list every X minutes ....

Good Luck !

Answer 2

I wrote an article a while back about the different methods for creating a CountDown on a list view, which is essentially what you're looking for:

Warning: This also mentions our own product as a possible solution

How to create a Countdown in a SharePoint list

Here is a summary of the options:

• JavaScript: Use a Content Editor Web Part.
• Designer: Create a custom view in SharePoint Designer.
• Code: Make your own custom field type from scratch.
• SharePoint Highlighter: Buy our product (notably this doesn't currently support Weeks, just Days and/or Months)

More specifically, I also wrote a (similarly product plugging) article on using [Today] in a list view:

How to use [Today] in a SharePoint list

This article gives more background on why [Today] doesn't work:

Unfortunately because of the much larger amount of information SharePoint has to pass back and forth, it takes a different approach to updating the data items. It will only update calculated values when the related item is added or edited.

Consider SharePoint as an overworked secretary; if you ask for a file to be updated they’ll find it in the filing cabinet, update the information, and correct anything else that’s awry on the paperwork while they’re there. If you ask for all the files for people named “Smith” to be put on your desk, unless you specifically say “And update the information on every single one while you’re at it”, it’s not likely they’ll do the extra work for no reason.

It also received the following rave review from the venerable Mike Walsh himself:

I also didn't think much of the way the advert section was written. Information it wasn't - overdone marketing it was.