calculated field - formula for ageing

Question

Again a calculated field question, How can I create a calculated field to find out, what is the age of the entry.

In the list I will have a created date field, from that I would like to create a number field calculating the no of days from created day to today. I tried with =Today-Created, not working!!

Any inputs ? Thanks !!

A similar question: http://stackoverflow.com/questions/878744/any-solution-to-the-today-calculated-column-problem-is-sharepoint — Andrey Markeev, Nov 02 '11 at 12:14

Andrey Markeev · Accepted Answer · 2011-11-02T13:23:49.997

Unfortunately, calculated column approach will not work as expected :( Calculated fields are updated only if the column itself is being updated, or the item is updated. You can fiddle with setting date to future in Windows to prove it.

So I would recommend you to use XSL transformations for XsltListViewWebPart in conjunction with dateUtils.xsl from Marc D Anderson. I'm using this method for displaying "Birthday is soon" indicator in Contacts lists:

enter image description here

Works like a charm.

Tentative code example (this will display "age" of item in days):

      <xsl:variable name="DaysToday">
        <xsl:call-template name="countDaysInDateWithLeapYearDays">
          <xsl:with-param name="paramDate" select="substring-before(ddwrt:TodayIso(),'T')"/>
        </xsl:call-template>
      </xsl:variable>

      <xsl:variable name="DaysCreated">
        <xsl:call-template name="countDaysInDateWithLeapYearDays">
          <xsl:with-param name="paramDate" select="ddwrt:FormatDateTime(string($thisNode/@Created),1033,'YYYY-MM-dd')"/>
        </xsl:call-template>
      </xsl:variable>

      <div class="indicator">
        <xsl:value-of select="number($DaysToday)-number($DaysCreated)" />
      </div>

You should place this code into PrintTableCellEcbAllowed xsl template. And don't forget to include date_templates.xsl :) To learn more about XsltListViewWebPart templates, read following MSDN article:

http://msdn.microsoft.com/en-us/library/ff604024.aspx

Thanks, omlin let me see how to get this worked in a list column, hopefully it will for each list item separately :) — Jithu, Nov 02 '11 at 12:03

score 3 · Answer 2 · answered Nov 02 '11 at 10:54

3

You can use Today with some trick, but the problem is that it will use the Today value from the day it was last modified. You'll then need to have something to modify the item each day/week/year, like a workflow.

http://weblogs.asp.net/bsimser/archive/2009/03/18/aging-calculated-fields-in-sharepoint.aspx

If its not used for any other logic, only for showing I would maybe used a javascript to calculate the difference, or use a CQWP with an itemstyle that does the calculations. Guess it depends what you'll be using the field for.

answered Nov 02 '11 at 10:54

Anders Aune

6,268
1
22
31

Hi Andez, Thanks for helping me again, I am just using this field only for our reference purpose, like we have a task generated and we need to know , how old that task is? And we have to display it in the list!! – Jithu Nov 02 '11 at 10:58
How can we use Javascript to calculate a column values, I really don't know that method!! – Jithu Nov 02 '11 at 11:11
Hi as you said, I just went through the link you provided, however the problem you stated is really a mess. However got a good link for updating the fields dynamically - here it is . Looks like its gonna be a long day for just getting some days calculated !! Thanks !! – Jithu Nov 02 '11 at 11:22

spevilgenius · Answer 3 · 2011-11-02T11:42:39.243

0

I have been successful with this formula: =DATEDIF([Created],[Today],"d") However, you have to create a Today column in the list leaving all the defaults. Then create this calculated column referencing that Today column. Then delete the Today column.

edited Nov 02 '11 at 11:42

answered Nov 02 '11 at 11:22

spevilgenius

1,341
10
18

Thank you spevil, I think still I should create a separate TODAY column!! – Jithu Nov 02 '11 at 11:25
Getting Error!! =DATEDIF([Created],[Today],"d") – Jithu Nov 02 '11 at 11:29
I corrected the entry. I should have better explained it! – spevilgenius Nov 02 '11 at 11:43
1

I'm afraid this will not work :( Based on my own experience. Also have a look to this post: http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/ – Andrey Markeev Nov 02 '11 at 11:56
@spevilgenius Will that also need to modify the entry daily to get the Today field worked? Should I need to enter any data inside the today field for each list items? – Jithu Nov 02 '11 at 11:58
@omlin please have a look at this article, will this help me? – Jithu Nov 02 '11 at 12:00
@Jithu, it probably will, but I reckon in your particular situation XSLT will fit much better. All you will need is a short XSL transformation - without all this complex crap with timer jobs/console apps. – Andrey Markeev Nov 02 '11 at 12:14
1

My solution works for what I used it for. However, I like the XSL approach and Marc's stylesheet is very useful. – spevilgenius Nov 02 '11 at 14:44

score 0 · Answer 4 · answered May 17 '12 at 10:28

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 (well, a CountUp):

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.

calculated field - formula for ageing

4 Answers4

Linked