Count not updating in calculated column

Question

I am getting number of days between Created & Now in SharePoint 2010 list using calculated column by below mentioned formula.

=IF(ISERROR(DATEDIF(NOW(),Created,"d")),DATEDIF(Created,NOW(),"d"))

It gets me the correct value.

But the problem is that the counts are not updating on regular basis.

e.g.

Imagine that i had created item in list before 5 days & if i create calculated column using above formula than it will give me correct count (5) but if i go to list on next day than it is showing me the same count. It is not updating to 6.

Can you please help me on this?

Answer 1

Try this.

Create one Calculated column and set data type return from formula as Number. Apply below formula:

="<img src='/_layouts/images/blank.gif' onload=""{"
&" var day=new Date();"&" var SPday=new Date(); "
&"  SPday.setFullYear("&YEAR(Created)&","&MONTH(Created)-1&","&DAY(Created)&");"
&" var m = Math.ceil(Math.abs(SPday.getTime()-day.getTime()) /(1000*3600*24)); "
&" this.parentNode.innerHTML= m ;"
&"}"">"

Let me know if it works or not.

Answer 2

Calculated Formulas are only modified/updated when an Item Changes

Read that one more time:

Calculated Formulas are only modified/updated when an Item Changes

Got it?

That means the Today/Now function will get you the result from: the LAST item update.

SharePoints Today/Now functions do not behave the same as in Excel.

You can force such an update with:

• Changing the Formula that's why most people think Today()/Now() works
• Changing (any value of) the Item:
  • With a Workflow
  • or Code (.Net or JavaScript)

Long explanation, workarounds an bag of tricks at:

How to use Today and Me in Calculated column

Answer 3

Values in calculated columns are not dynamic. They are recalculated when list item is updated.