I've been tasked with getting a SharePoint site up and running as part of my job, it requires a semi-automated way of marking files for an archival process where they are taken off of the SharePoint and moved to a shared drive to conserve space. This is also part of a process to move many files on to the SharePoint system which may override important information stored on the files such as the date it was last modified, for this reason I would be utilizing a column which is effectively the last modified date of the file before its transferral to SharePoint.

Right now my brain is kinda working on this problem in pseudo-code of sorts, I have some decent experience with PHP and Java and mostly understand how the logic behind this should work, I'm just having trouble bringing it to life.

What I have so far is a collection of columns for files, the main ones relevant to my problem here are a File Activity metadata which should be a calculated column that can determine the contents of the field depending on other factors/columns.

Another column is for a Retention Period End Date, of which after this date is reached the file should be pushed to review by a user and then its fate should be decided (postpone archival/archive it/delete it, this is handled in another metadata column that is manually selected called Review Status).

Finally, the last relevant column to this idea is a [Real Document Date] which is the date the file was last modified before transferring it to the SharePoint system, since [Last Modified] would be overwritten as soon as it was added, as I've touched on above.

Anyway, the File Activity metadata should contain three possible outcomes: Active, Dormant or Needs Review.

My pseudocode for this sort of process is as follows:

IF [Last Modified] < 6 months
= "Active"

IF [Last Modified] > 6 months
= "Dormant"

IF [Last Modified] OR [Real Document Date] > [Retention Period End Date]
= "Needs Review"

I have tried a few different ways I could best understand how to implement this idea as a calculated column based on other people's questions and answers given on here and modifying their code for my own use, but I've failed each time. I was wondering if anyone more experienced with calculated columns in SharePoint could effectively spoonfeed me the solution to my conundrum?

Thank you for your time.

1 Answer 1


Please try below formula. Kindly make sure column names are correct. I have copied from your question

=IF(OR([Modified] < [Retention Period End Date],[Real Document Date] < [Retention Period End Date]), "Needs Review",IF(MONTH([Modified]) < 6, "Active","Dormant" ))

Below tutorial might help you better design calculated column



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.