How do I create a calculated conditional column using today's date, and automatically update each field daily?

Question

I have created the following list (Timeframe) in SharePoint;

• Timeframe
  • Start Date
  • End Date

Another column, [Is Current Timeframe], needs to be created that specifying whether or not the Timeframe item is the current timeframe. That is, if today's date falls between [Start Date] and [End Date], the new column should be populated with "Yes", otherwise "No".

Pseudocode:

if [today's date] is between [Start Date] and [End Date]
then [Is Current Timeframe] = Yes
else [Is Current Timeframe] = No

The formula I tried using returned an error, and was not correct;

=IF (AND([Today]>=[Start Date], [Today]<=[End Date]), "Yes", "No")

What am I doing wrong with the formula?

Also, what would be the best way to update [Is Current Timeframe] daily? That is, the value of [Is Current Timeframe] should be recalculated each day to make sure it is correct.

Answer 1

Unfortunately, this isn't possible since Calculated Columns are not recalculated on viewing, but only on creation and modification. See this article: http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/

You will need to solve this with display logic.

Answer 2

Rather than a timer job which "touches" all the items, consider using jsLink or other client side rendering techniques to calculate the value in real time.

Of course, it depends on how you are using the values, which you don't indicate.

Answer 3

The way I got it to work was with a timer job.

1. Create a timer job
2. Itrate over all the items in the list
3. Do an item.systemupdate()

This won't change any field but will sure recalculate all the calculated colums.

Answer 4

Maybe type 'calculated column today' in that white box in the top right (it is a search box) And switch to the ranking-by-votes ??

https://sharepoint.stackexchange.com/search?tab=votes&q=calculated%20column%20today