I had a recent request I wanted to share with everyone. This question centered around how to use calculated columns and SharePoint functions.
I’ll note here that the functions that are available to Excel are similar but not exactly the same as SharePoint’s functions.
A good primer guide to SharePoint functions can be found here:
Microsoft Office/Support/ SharePoint/ Windows SharePoint Services/ User Documentation and Resources
So in my example I wanted to create a new view, and display 2 days (today and yesterday) worth of documents based on a custom column I specified called ‘event date’. You can use any date field (e.g. created, modified) I’m using this as an example only.
So to start we have a document library that looks like this…
Add Custom Column
Great, but not what we really need. It is missing the ‘event date’ and the filtering. Our next step it to add the custom column. We do this by selecting Library Tools-> Library-> Create Column.
I populated our newly created custom column ‘Event Date’, but we still need to filter it to only show the last 2 days.
Add Custom Filter
Now create a new view called ‘Last 2 Days Events’ with the new filter. Click on Library Tools->Library-> Create View-> Standard View
Lets enter the name and the important part is the ‘Filter’ section.
I only wanted to see events for the last two days, which are today and yesterday. In the filter fields I based the calculation criteria ‘is greater than or equal to’ for ‘[Today-1]‘. this give me the following in the view.
Remember you can use any standard of custom date column to do these calculations. So to further example you can create a views to show…
- Documents modified/added with in x days
- Average number of doucments added per day
- …and so on.
While I only worked with document libraries in this example the same applies to any SharePoint list that uses views.