Can I create a calculated column that only upddates when it's a new item?

Question

I have a list with an editable due date column. I want to be able to conserve the first value saved of the due date column, If the due date value is changed. So I created a second column called DD2 as a calculated field.

For the DD2 column, If I use the formula: =[Due Date] It's going to update [DD2] every time [Due Date] is changed

If I use the formula: =IF(Created=Modified), [Due Date], "") then it works for the new item but all saved items will overwrite DD2 with a blank

If I use the formulate =IF(Created=Modified), [Due Date],) then it works for the new item but all subsequent saved items will overwrite DD2 with an obscure date "12/30/1899".

How can I tell it to make DD2=[Due Date] only when it's a new item, and do nothing otherwise?

Answer 1

In short, a calculated column won't work for what you want to do. Calculated columns update themselves everytime a list item changes, and you can't use their existing values in the new result (a circular reference).

A more applicable solution to your situation is a workflow that runs each time an item is created (you would need to make due date a required field in this case) then sets the value to some other field which won't be changed later. You could also use an event reciever, but they generally require more work and server access to get setup.

As an aside, your calculated formula needs a third parameter to not output the 'empty' date value 12/30/1899

=IF(Created=Modified), [Due Date], "")

Note the additional empty string value -- but again this doesn't really provide the behavior that you're looking for...

Answer 2

I would probably do it with 2 date columns.

One column would be hidden on the form (via management of content types, hide the column) and the default value would be set to today. This would serve as the gold value. Then a second editable date column is available and would default to today, but would be visible so it can be changed.

Answer 3

You will have to write some code here. Create an event receiver. Create a single line of text field and hide it from new and edit forms. In the event receiver, catch the Item Adding event and then update the field. A simple set of code lines.