Help with a calculated sharepoint field?

We have MOSS 2007 installed. I'd like to create a calculated field that does some magic logic for me :) As background, I have the following fields:

Created (Date)
ApprovedDate (Date)

I'd like to add 1 calculated field...   where if status = PENDING, it does "today - created" to show # of days pending.
If status = APPROVED or REJECTED, it should do "ApprovedDate - created" to show # of days that elapsed before a decision was made.

Is that possible to do in 1 field?
Who is Participating?
abhitrigConnect With a Mentor Commented:
for the formula, yes. The problem however is that the "today" value won't get refreshed the next day.

So you will get a correct value at the time of item creation it won't refresh the next day. Option, use a workflow to manage/update the values.

BTW, your formula will be something like this =if([Status]="PENDING",Today-Created,ApprovedDate - Created). You will need to create a dummy column called "today" to get the formula working.
abhitrig, pointed out the flaw regarding the calculated field not updating.

One possible solution would be to write a powershell script to "touch" the each item in the last daily.
leopoldeConnect With a Mentor Commented:
In order to update the items daily (and ensure your calculated field is updated), you might want to try to create two small workflows in SharePoint Designer that basically do the following for every item:

1st Workflow (Run on new and modiefied items)
* Sleep for 24 hours
* If the item status is "PENDING", then update a dummy field in the item called something like "Loop" so it contains a "Yes"

2nd Workflow (Run on modified items)
* If the field "Loop" is "Yes" then change it to "No".

The reason you need two workflows for this is that a workflow cannot start itself.  Remember to create the "Loop" field, with a default value of "No"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.