I'm not sure if this is possible, but I'll ask anyway.

We have items that have a field called "Status", and we'd like metrics (which is where the calculated field comes in) for how long (in days) was it between when the item was created and when "Status" went to "Approved" for the approved items. It may get modified after this, so I can't just assume it is the "Last Modified", though I may have to and then just use that with the grain of salt.

So can the calculated field be used to look at when the field went to "Approved" and return that date, and then do the calculation? Or am I stuck with DATEDIF([Created], [Last Modified], "d").

I've reviewed the MSDN documentation on Calculated Fields, here: http://msdn.microsoft.com/en-us/library/bb862071(v=office.12).aspx

Thanks in advance.


I think you'd need to do it with a workflow/event receiver to check the status field and set an approved date field when the item changes. Then you'd calculate off of created and the approved date to get your metric.

Using modified like you said isn't going to be reliable.

  • This would work for new items. I'd have to go through and modify each item to kick off the workflow to update each old item, and there's 159 approved items. I could do it, I guess, but is there a faster way? And how would the workflow know when the approved date was? I could build a custom .NET workflow to get it from version history, but they don't do managed code where I work. Possible in SP Declarative WF? – vapcguy Aug 28 '15 at 20:12
  • it wouldn't be accurate for anything previously approved, you'd have to throw that data out essentially, unless you update the field value before deploying the workflow or event receiver – Eric Alexander Aug 28 '15 at 20:16
  • 1
    I bit the bullet. I created a new column called "Approved Date". It took 3 hours, but I went to the Version History of each item, grabbed the date the Status field was updated to "Approved", and recorded this in the new field for the old items. Maybe I coulda scripted it, but anyway... Your fix will work going forward on new items, though - I will have the workflow update the new field. I also have the calculated field "Time to Approve" doing =DATEDIF([Created], [Approval Date], "d") and so it will auto-calculate when the Approval Date gets filled in via workflow. Thanks! – vapcguy Aug 28 '15 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.