Can SharePoint column be changed from date/time to calculated date/time

Question

I have a SharePoint list with a column that was originally defined as date/time and has been manually populated with date/time information. I'd really like to populate this column automatically based on the status of another field changing (e.g. =IF([theStatus]="Closed",[Modified]).

When I edit this date field that I'd like to add the above code, I paste the script into the "Calculated" field and when saving, I get the following error:

The formula contains reference(s) to field(s).

Would be greatful to know if I'm doing something wrong or SharePoint just doesn't allow this type of modification.

Thank you.

Answer 1

In the case of a DateTime field, the "calculated" default value is primarily for things like =Today+1. You cannot reference other fields, unfortunately, really the only thing you can reference as a variable is Today. In order to reference a field in a calculation, you would need to use a Calculated field type configured to display a DateTime, instead of a DateTime field type. Unfortunately you cannot convert an existing DateTime field into a Calculated field, so you would have to create a new field.

If you wanted to play in the object model, though, you could always use a workflow or event handler. I think a SharePoint Designer workflow could also do the trick, if you went that route.

Answer 2

Actually, I did something like this. From an user entered date and time, I had to build a user define code, something like "2009/12/13 07:05-AVALTG23" for example.

I did not come with any other way:

• Creating a custom action (in VS2005) that, given a date time, returned a string in the format and with the operations I wanted. Since you are programming your custom action and returning a string, you can pretty much do whatever you want.
• When an object was create, a Workflow starts, which populates my "Custom Code" field.

So, for anything so simple as getting a column value, formatting it and populting another, I had to develop a custom activity and create a workflow.... Lots of work, but pretty much the only way I could find.

Hope this helps!