How to Create a Calculated Column in Microsoft Lists in Teams or Sharepoint - Microsoft List Tips

hey nerds Amy here and today we are going to learn how to set up a calculated column in Microsoft list all right let's nerd out [Music] here at Amy's animal barn and Petco we have a dog boarding Resort uh where your pets get to Vacation while you are also on vacation here we have a list where we track dog stays and we want to add a column to calculate the total cost for their stay which will include a discount for seven plus nights okay so what we are going to do is click on ADD column and scroll down to see all column types now a quick sidebar if you don't see this option here then open up your list in SharePoint list let's click on see all column types and click next okay so here we will now um add a column name which we will do knights and if you scroll down you'll see this calculated option so if we click on that then it will automatically populate all of the different columns that we already have in the list under insert columns here now we will come back to that in just a moment but I did want to scroll down a little further so here we have the data type return from this formula so this is going to be the output option and as you can see we actually do have quite a few options available however there are some limitations such as you can't designate a person within your organization now that's okay for this video we are going to do number for the number of nights by selecting number you'll also see that we have an option to add decimal places which we're going to select zero and there's also an option to change this to a percentage for the purpose of this video that's not applicable so we will just move on okay so now we are going to create the formula to calculate the number of nights similar to excel we will start with an equal sign and then all that we're going to do is just double click on these existing Fields so check out then a minus sign double click the check-in and there we go let's scroll down and hit OK all right and now just a sidebar you will notice that there are no spaces when we are entering those formulas now look at that we have a column that calculates the number of nights based on the check-in and checkout Fields already defined in this list next we will calculate an if statement this is going to look a little bit different because it is an if statement once again we will start off with an equal sign go if bracket we are going to go Knights double click are greater than 6. then the true component is going to be night double click times 25 minus 25 so that's how we're going to get that seventh night free comma now we are going to go to if this condition is not met times 25 closing bracket and this time we are going to select currency for the output and let's select zero decimal places again here it will show a currency format we'll leave that as is for this video and click ok ok look at that we now have this cost column that calculates the cost for the stay based on the number of nights and look at that it is working correctly because Charles here that is staying for seven nights is paying the same amount as Rue who's staying for six nights meaning they are getting that one night free and here we have Layla for three nights at 75 so that is working correctly now before we move on I did just want to add that if you want to edit this field you may notice that it is not an option when you click on this format column as you would with other column types so to do that we are going to navigate to the settings area up here click on list settings then if you scroll down you will notice all of of your column types here and if you click on cost then that brings you back up to this area where you can now edit the formula before we move on to the next section if you do find this video helpful then please give it a thumbs up and hit that subscribe button it would really mean a lot to me okay so moving on to limitations this is not the complete list of limitations but these are some things that I felt were noteworthy that I wanted to share with you okay the last thing that I did want to touch base on was some tips to help you play around there are a ton of options check out the link in my video description for examples of common formulas alright so that wraps up this video and I do hope that I have helped you discover your inner nerd along the way if you did like my video then please give it a thumbs up and hit that subscribe button additionally if you do have any questions then pop those in the comments below alright thanks for nerding out we'll see you again

Channel: Office Skills with Amy

Views: 17,200

Keywords: calculated column, calculated column in lists, calculated column in microsoft lists, calculated column in sharepoint lists, calculating dates in microsoft lists, creating calculated column in microsoft lists, editing a calculated column in microsoft lists, how to create a calculated column in lists, how to create a calculated column in microsoft lists, how to delete a calculated column in microsoft lists, ms lists, sharepoint lists advanced, ms lists in teams, ms lists advanced

Id: EQmw6x4AatM

Length: 5min 52sec (352 seconds)

Published: Tue Apr 11 2023