Sharepoint - Calculated Column showing different colours based on list item age

Question

Marc,

I understand you are a busy man, but if you can spare atleast sometime to my query below then I will really appreciate the gesture.

Background:

We get several requests (items on SharePoint list) on a daily basis. We assign different request status to different requests. We need to track ageing of a request in a particular request.

How long has the request being in a particular status?

My requirements:

We have column called ‘Request Status’. If this column value for an item is say 'XYZ', then I need to check for the column ‘Modified’. This modified column is a sharepoint default which shows last modified date.

Now, I will compare this column ‘Modified’ with today’s date and do following:

1. Have a calculated column ‘Ageing’ which will calculate for each request (item on SharePoint) its age since its last modified date(in days). Based on this ageing date different traffic signal lights will be shown in this column.

So, if ageing date is

0-1 days: Green Light

1-2 days: Amber Light

Beyond 2 days: Red Light

Limitations: 1. Implementation will be inside company intranet and hence Sharepoint Designer does not work. Only way is Content Editor Web parts through scripts.

1. I am very new to Sharepoint thingy. Gone through several solutions suggested in various sites like paths.to-sharepoint but alas couldnt customize them to what I was looking and it didnt work. Most of these were 'countdown' and hence had to customize which I couldnt do.

2. I have entries of a list grouped by, so it should work with this arrangement.

---

Any help from anyone will be very much appreciated as I need to implement this solution as soon as possible.

Thanks

Answer 1

Calculated columns are only recalculated on a change of a list item, so it doesn't work to use one for your purpose here. (It would continue to show the elapsed time between modified and the last save date until another save occurs.)

You can, however, use script to augment the view you are displaying by grabbing the modified date, doing the date math, and altering the DOM as necessary.

Answer 2

I've written two articles on our company blog that address the two aspects of this problem. Each has a section on Javascript (linking to Christophe's blog, which you've mentioned you've seen), Designer, freebies, coding your own, and of course our own product (SharePoint Highlighter):

Icons: How to create a SharePoint KPI column

Using [Today] in the list view: How to use [Today] in a SharePoint list

However, I'm afraid I'm going to have to blow our trumpet even more blatantly, as it's quite straightforward to produce the exact results you're after, using Highlighter. Here's a screenshot of the settings you would need:

Answer 3

If you are ok with a client side script, there are several solutions on my blog:

http://blog.pathtosharepoint.com/?s=countdown

(Search for "countdown")

You can also follow the instructions on this page:

http://usermanagedsolutions.com/SharePoint-User-Toolkit/Pages/Countdown-Countup.aspx

A completely different approach that could work in your case is to create view filters (e.g. get all items where modified < [Today] -2). Note that you can't use Today in calculated columns, but you can use it in filter rules.