Calculated field for Date column

In a SharePoint list or document library, we can create a calculated field. This field becomes handy when we have to display a formatted/calculated value based on the existing column. We often hear the requirements wherein we have to display a formatted value of the Date column.

Ex., Display only Month value of a date column, display only Month and year part of the date column and so on. Let me show you how we can achieve this:

For this example, create a list having a date field. When you create a list, by default, SharePoint adds two date fields "Modified" and "Created". So you can use any of these columns or you can create a custom Date field.

After creating a list, now create a column of type "Calculated". Under the Formula text box, we need to enter the formula which will fetch us the desired value. In my example, I'm going to use a custom date field "StartDate".

I want to display Month of the StartDate column in 3 characters like Jan, Feb. I'll use the below formula for that:

=TEXT(StartDate,"mmm")

Here, second parameter decides the format for the Date.

Now, I want to extend this to display something like this: Jan 2009, Oct 2010. I'll change the formula as:

=TEXT(StartDate,"mmm")&" "&YEAR(StartDate)

Let us see one complex formula. I want to calculate Quarter based on the date: Something like Q1, Q2 etc. The formula for this looks something like this:

=IF(AND(MONTH(StartDate)>=10,MONTH(StartDate)<=12),"Q3",IF(AND(MONTH(StartDate)>=1,MONTH(StartDate)<=3),"Q4",IF(AND(MONTH(StartDate)>=4,MONTH(StartDate)<=6),"Q1",IF(AND(MONTH(StartDate)>=7,MONTH(StartDate)<=9),"Q2",""))))

This is a nested IF condition using AND logical condition. The output looks something like this:

Similarly, we can use calculated column effectively to display formatted data. In my next post, I'm going to show you some more formulae.

Comments

All about SharePoint List View Styles

Sometimes, there are out of the box features which we tend to ignore and later when we do apply, we are more than happy about the feature which is readily available in SharePoint. One such feature is List View Style. I never thought I would write a post on this. However, whenever I spoke about this with users, people were excited to see the result. That prompted me to write this post. Instead of getting into only theory part, I will basically take use cases where these styles can be applied and also touch up on on some minor limitations with certain style. When you are creating/modifying a List view, you will get an option to select View Style. As shown below, there are 8 options available and Default is always set if you ignore this style. List of View Styles I will take typical Contact List and Announcement List to explian about these styles. Let us go one by one. Default: This view, as name suggest, is the default style in a view. This is one of the widely seen style ...

Document ID Service - New Feature in SharePoint

When I explored this new feature of SharePoint in Online (this feature is available in SharePoint 2013 also), I found it interesting because it solved one of the common problems people faced in SharePoint. I will first explain about this feature and I'll cover the problem and the solution in another post. This feature allows Site Collection Admin assign a unique ID to every document in the site collection out of the box. If you recall, there was no out of the box way to assign a unique number to the documents across libraries in a site collection. Yes ID column is unique but only within a library. Document ID column contains a number which is unique across libraries. To start with, let's have a look at a typical document library. This is a familiar view for SP guys. By default, you don't get Document ID column in a library. All Items View To get Document ID column, you need to activate a site collection feature "Document ID Service" as shown below. S...

How to update Person field with multiple values using REST API

Person or Group field in SharePoint is similar to a Lookup field. When you are updating this field using REST API, you need to append "Id" to the name of the column in the body construct. For example, the body construct looks like this: data: { "__metadata": { "type": "SP.Data. ListName ListItem" }, "Title": "First Item", " PeopleField Id": "4" }; The highlighted portions should be replaced by the actual List Name and Column Name. In the above example, the REST call is updating a List item with Title and People columns. How to get the value for user ID ("4" in the above example) needs a separate explanation and that will be my next post! The above example works fine if Person field is configured to accept only one value. If we change the Person field to accept multiple values, how do we pass more than one value in the REST call? Since we normally separate user names with semicolon in peop...

SharePoint

Search This Blog