In SharePoint foundation 2010 can i add a new calculated value column where the calculation are based on when another column is modified ?
Out of the box, SharePoint won't tell you when a specific column was modified. You only know when the item was last modified (Modified field).
To track changes on a specific column, you would need to duplicate it and run a workflow that checks for updates. Not a simple design.
Here is how it would work:
- add two more columns hidden from the users: CopyColumn and CopyModified
- on item change, the workflow compares Column with CopyColumn
- if the values are different it means that the user has modified the column. Calculate the time difference between Modified and CopyModified, then update CopyModified and CopyColumn for the next round
- if the values are the same, no action, just stop the workflow
Yes, you can see this link for possible formulas - http://msdn.microsoft.com/en-us/library/bb862071.aspx
Agree with Christophe. SharePoint has no column level changetracking (just like there's no column level security). A list item is the deepest object in SharePoint you can bind events etc. to. My current client wanted to be able to see (and track, for auditing) purposes these column changes as well. We implemented an ItemEvenReceiver that writes all fields for which the AfterProperties value of a column didn't match the BeforeProperties value of said column to a database.
Another option would be to have a list identical to the source list and copy the item to that list on every update (or just the date and the old and new value of the column in question).
And another option would be to add a column ColumnBModified to your list and setting that column to the current time when the BeforeProperties value of a column does not match the AfterProperties value.
All options require code (ItemEventReceiver)