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

0
votes

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 grateful to know if I'm doing something wrong or SharePoint just doesn't allow this type of modification.

2
sharepoint
Just re-read this about a half dozen times and I think you need to clarify what you are trying to do here. I read it as when the status is changed to closed then put the current data into a field called as "Closed Date" so you've got a permanent record of when the record was closed. If so then event receivers/workflow is the only way you will be able to do this. Please clarify your question. – Ryan

2 Answers

2
votes

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.

1
votes

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!