SharePoint List ID Calculator Chi tiết

Thủ Thuật về SharePoint List ID Calculator Mới Nhất

Bạn đang tìm kiếm từ khóa SharePoint List ID Calculator được Cập Nhật vào lúc : 2022-12-19 11:58:07 . Với phương châm chia sẻ Kinh Nghiệm Hướng dẫn trong nội dung bài viết một cách Chi Tiết Mới Nhất. Nếu sau khi tìm hiểu thêm tài liệu vẫn ko hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Tác giả lý giải và hướng dẫn lại nha.

In this SharePoint tutorial, we will discuss what is a calculated column in SharePoint? How to create a calculated column in SharePoint list or library? And we will also check out a few SharePoint calculated column examples.

Nội dung chính

• What is a calculated column in SharePoint?


• How to create a calculated column in SharePoint List


• SharePoint Calculated Column Examples


• 1. Create hyperlink in SharePoint calculated column


• 2. SharePoint list column color based on value using calculated column


• 3. SharePoint calculated column concatenate


• 4. SharePoint calculated column year from date


• 5. SharePoint calculated column difference between two dates


• 
  

What is a calculated column in SharePoint?




In SharePoint, we can use calculated columns that will calculate values based on another column in the same SharePoint list or library. Here we will use a formula to calculate the value and these formulas are based on Microsoft excel functions and syntax.




Below are the type of formulas we can use in a SharePoint calculated column.



• Conditional formulas


• Date and time formulas


• Mathematical formulas


• Text formulas, etc.



Check out this msdn link to know the formulas that are supported in a calculated column in SharePoint.




Complete SharePoint Training Course Just for $199 (Just for Today)




How to create a calculated column in SharePoint List




First of all, let me tell you, we can create a calculated column in SharePoint list, library, and site level.




Follow the below steps to create a calculated column in SharePoint Online list or library. The same steps, you can follow to create a calculated column in SharePoint 2013, SharePoint 2022, and SharePoint 2022.




Step-1:




Open the SharePoint list or library where you want to create the calculated column. Then click on + Add column -> then click on More




create a calculated column in SharePoint List




Then it will open the Create column page, here provide a Column name and then choose the Type as Calculated like below:




create a calculated column in SharePoint Library




Next, the Additional Column Settings section is very important. Here we need to define the Formula for the calculated column.




For example, I want to set the Course Completion Date as Created Date + 60 Days. So I will write the formula like below:




=[Created]+60How to create a calculated column in SharePoint List or Library




Note: We can not use the [TODAY] and [ME] in SharePoint calculated columns.




Then click on the Save button, and the calculated column will be created in the SharePoint Online list. And it looks like below:




How to create a calculated column in SharePoint List




This is how we can create a calculated column in SharePoint Online list or document library.




SharePoint Calculated Column Examples




Below are a few examples of calculated columns in SharePoint Online and everything will work in SharePoint On-premise versions like SharePoint 2013/2022 or SharePoint 2022 also.




1. Create hyperlink in SharePoint calculated column




Let us see, how to create a hyperlink in SharePoint calculated column. Particularly, we will create an URL by using the SharePoint calculated column. In the same way, we can also create a URL using a calculated column in SharePoint 2022/2022/Online.




By default when the user clicks on the Title column of a list then it usually opens the view form. But our requirement was to navigate to a different page with some query string parameter.




Generate URL using Calculated column in SharePoint List




For this we have created a calculated column with a formula like below:




=”<a href=”https://www.enjoysharepoint.com?ID=”&ID&””>”&Title&”</a>”




Here it will take the ID as the query string parameter.




And one more thing we should remember here is that we should choose Number in The data type returned from this formula is: section. It should look like below:




Create hyperlink in SharePoint calculated column




After this when you see in the view, you will be able to see the hyperlink column.




Create hyperlink in SharePoint calculated column




Another example we can create an email link, when the user clicks on the link it will open the email window, and then in the body toàn thân, it will put the URL.




=CONCATENATE(“<DIV align=””left””><a href=””mailto: ?SUBJECT= &BODY=https://www.enjoysharepoint.com?ID=”,ID,”””>email this post</a></DIV>”)




In this way, we can create hyperlink column or URL by using the above formula in a calculated column,




2. SharePoint list column color based on value using calculated column




This calculated column example is all about sharepoint calculated column if. We will see how to use the sharepoint calculated column if condition.




We will see here, how to color code SharePoint list column value based on condition using the calculated column in SharePoint.




This example explains, how to show colors based on status column value in SharePoint 2013 list using the calculated column.




Here I have a SharePoint list that has a choice column known as the Status column and it has values like:



• Started


• InProgress


• Incomplete


• Critical


• Done



So based on this value we want to show different color in the list view so that by color it will be easy to differentiate from one another in the SharePoint list.




For this, we took a calculated column and in that calculated column we put the below logic like below:




=”<div style=”text-align:center; width:100%; background-color:”&IF([Status]=”Started”,”#c0c0c0;”> &nbsp&nbsp </div>”,IF([Status]=”InProgress”,”#FFFF00;’> &nbsp&nbsp </div>”,IF([Status]=”Incomplete”,”#FF9900 ;’>&nbsp&nbsp </div> “,IF([Status]=”Critical”,”#FF0000;’> &nbsp&nbsp </div>”,IF([Status]=”Done”,”#00B050;’> &nbsp&nbsp </div>”)))))




And also make sure that you need to choose Number in the The data type returned from this formula is: section for the calculated column. It will look like below:




sharepoint calculated column if




After this based on the status column in the list, the color will come like below:




sharepoint calculated column if condition




This is how to color-code SharePoint list column value based on condition using the calculated column in SharePoint.




3. SharePoint calculated column concatenate




This is an interesting example of SharePoint calculated column concatenate. Here, we will see, how to concatenate two columns in SharePoint list using a calculated column.




Let us see, how to concatenate two list columns in SharePoint 2013 using the SharePoint calculated column.




Here in this example, I have a SharePoint list that has two columns name column1 and column2. And we are concatenating these two columns and displaying the result in a 3rd column using the calculated column in SharePoint 2013/2022/Online.




Here first two columns in the SharePoint list and then create another column of type calculated column. And add the formula like below:




=CONCATENATE(column1,”-“,column2)




You can check out the formula how it looks like:




sharepoint calculated column concatenate




Now if you add an item to the SharePoint list then the concatenate value will appear in the 3rd column like below:




sharepoint list concatenate two columns




This is how to use CONCATENATE function in SharePoint list calculated column to concatenate multiple columns in SharePoint 2013/2022/Online List. This is how we can use sharepoint concatenate in a calculated column.




4. SharePoint calculated column year from date




Let us see another example of how to get month and year from date in sharepoint calculated column? We will see, how to get year from date using SharePoint calculated column.




I will explain SharePoint calculated column get month and year from the date in SharePoint 2013/2022/Online. We will discuss how to extract Year and Month from the created date column in SharePoint 2013/2022. Here we will extract through a calculated column and store it in two separate columns in the SharePoint Online list.




SharePoint 2013: Calculated column to get month and year from date




Here I have created few calculated columns in the SharePoint list and I will show you how to retrieve year and month from created date column.




Go to the SharePoint list settings and then click on Create column to add a new column. Give the column name and chose the column type as a calculated column. Then in the formula write like below:




=TEXT([Created],yyyy)




The SharePoint calculated list columns looks like below:




sharepoint calculated column year from date




The above formula will return the formula as a single line text, Now if you want to return as integer then you can write the formula like below:




=YEAR(Created)




Similarly if you want to calculate month from created date, then follow the above step and write the formula like below:




=Text(Created, MMMM): This will return the full month name
=Text(Created, MMM): This will return first 3 letters of the month name
=Text(Created, MM): This will return integer month




If you want to return the value as an integer then you can write the formula like below:
=MONTH(Created)




Now if you will check the output will come like below:




get month and year from date in sharepoint calculated column




This is how to get month and year from date in sharepoint calculated column. I hope you like an example on SharePoint calculated column year from date.




Here, we saw, how to use sharepoint calculated column get month and year from created date column in SharePoint 2013/2022/2022/Online.




5. SharePoint calculated column difference between two dates




Let us see another example on SharePoint calculated column difference between two dates.




Recently, I was working on Project where There was a requirement to display the duration in Hour and Minute between two dates in SharePoint. In this article, I am going to explain how to retrieve the difference between two dates in hour and minute by using a calculated column in SharePoint 2013.




Follow the below steps:
Step-1: Create a SharePoint List having two columns of Date and Time Type as below:




sharepoint calculated column difference between two dates




Step-2: Now create a Lookup column BookedDuration(This Column will automatically calculate duration between the above Column).




sharepoint calculated column difference between two dates in hours




The Result will be Shown as below:




sharepoint calculated column difference between two dates




This is how to calculate the duration between two dates in SharePoint List using the SharePoint calculated column.




You may like the following SharePoint tutorials:



• SPFx Bind dropdown list from SharePoint list using PnP


• SharePoint list templates


• Display SharePoint list data in jQuery data table using Rest API


• Difference between SharePoint list and Document library


• Export a SharePoint list to excel


• Convert SharePoint list item to PDF using Flow or Power Automate



In this SharePoint tutorial, we learned about, SharePoint calculated column and 5 examples of how to use calculated column in SharePoint list or document library.



• What is a calculated column in SharePoint?


• How to create a calculated column in SharePoint List


• SharePoint Calculated Column Examples


• Create hyperlink in SharePoint calculated column


• SharePoint list column color based on value using calculated column


• SharePoint calculated column concatenate


• SharePoint calculated column year from date


• SharePoint calculated column difference between two dates








I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2022/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com




Reply

1

0

Chia sẻ




Share Link Down SharePoint List ID Calculator miễn phí




Bạn vừa tìm hiểu thêm Post Với Một số hướng dẫn một cách rõ ràng hơn về Video SharePoint List ID Calculator tiên tiến và phát triển nhất và ShareLink Tải SharePoint List ID Calculator miễn phí.





Giải đáp vướng mắc về SharePoint List ID Calculator




Nếu sau khi đọc nội dung bài viết SharePoint List ID Calculator vẫn chưa hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Ad lý giải và hướng dẫn lại nha

#SharePoint #List #Calculator