SharePoint Calculated Column as a Duration Counter in a List

I recently had a business request from one of my users to help tweak a Request Tracking List. The user essentially wanted to know count the number of days a request had been open, and once a request is complete the counter should stop. This is very useful information to track and is simple to accomplish with a calculated column if you understand how SharePoint works.

First, let’s get your custom list in order by adding a few columns:

• Create a Status column
• Create a column to hold today’s date. Whoa – but wait wait wait! you say. If you know anything about using a [Today] variable in SharePoint it can be a problem. That’s exactly why we’re creating this column…we can use it in our calculated columns instead of using the [Today] variable. Let’s make it hidden to avoid confusion.

• Create a starting date for your item (note: your starting date can actually be your Created date, but in my case the start date is when I have finished entering the request…not when I created it)

Now for the fun part!

• Create a calculated column to hold the Duration for the request. Remember our initial requirement – show the number of days a request has been open, but once the request is closed you need to stop the counter. This sounds a lot like it could be an IF statement, right? Click here to learn about Excel IF logic

Calculated columns in SharePoint work like Excel formulas so Excel is a great place to test. I created some dummy data in a spreadsheet just to test out my work. Here’s what my Excel scratch work looked like:

Once I settled on a formula I just translated it to SharePoint as seen above.

=IF([Request Status]=”Complete”, [Modified]-[Request Date],[Today]-[Request Date])

(note: My assumptions in this case are that the list item isn’t touched again after it is marked as complete, and that the counter should always continue until the item is marked “Complete”)

And there you have it – a quick way to calculate a duration for a list item that stops counting when the item is complete.

Summary:

1. Create a column to hold the status of the item
2. Create a column to hold today’s date. Using [Today] as a variable can be problematic.
3. Decide on a starting date for your item…this can be the item Created date or another date column
4. Create a calculated column to start and stop the Duration

I hope that you found this quick solution helpful!