Skip to main content

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

How to get SharePoint List or Library GUID via REST

Sometimes, you would need List or Library GUID to use that in some operation. In such cases, how do you get hold of GUID using REST API? There is a straight-forward end point which you can use: /_api/web/lists/getByTitle('ListTitle')/Id This will return the GUID of a List or Library. There is also another approach. However this approach works only if a List or Library contains at least one item. /_api/web/lists/getByTitle('ListTitle')/items This is a familiar end point which we use to fetch items of a List or Library. To get the GUID from the response, you would do: var listIDTemp = response.data.d.results[0].__metadata.id; var listID = listIDTemp.substring(listIDTemp.lastIndexOf("guid") + 5).split('\'')[0]; As you can see, we are doing string operations to fetch GUID from metadata.id. This contains information only if the response has at least one item.
Post a Comment
Read more

Get User Id using REST or JavaScript Object Model

Sometimes you would need to fetch the User Id based on either Login name or Email id. You would need User Id if you need to assign a user object to a people picker control or People/Group field. How do we get the Id based on Email or Login Name in client side development? We can achieve that using JavaScript Object Model or REST API. Let me share the first example using JavaScript Object Model (JSOM). var context = new SP.ClientContext.get_current(); this.user = context.get_web().ensureUser(loginName or Email); var o = { d: d, user: this.user }; context.load(this.user); context.executeQueryAsync(     Function.createDelegate(o, ensureUserSuccess),     Function.createDelegate(o, Fail) ); The above code fetches User Id for a given Login Name or Email Id. Interestingly, there is no equivalent endpoint available in REST! The Microsoft documentation talk about a endpoint but I could not get it working. So what is the way to get User Id using REST? You have to use the hidd
Post a Comment
Read more

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
1 comment
Read more