[2024] - How to Apply Custom Conditional Formatting to a Calculated Date Column in Microsoft Lists

haters Amy here and today we are going to learn how to add conditional formatting to a column in Microsoft lettuce just hold on there this isn't any conditional formatting this is conditional formatting based on a formula I.E and if statement it is very customized and I am so excited to show you so with that being said let's nerd out all right at Amy's animal barn and Petco we have created this list to track pet stays at our dog bony Resort and in this video we are going to learn how to insert a Json to format the status column based on today's date in reference to the check-in date this Json can also be applied to the check-in date but I like to have this second column here let's break it down step by step so that you can create this and implement it on your own the first thing that I did was create this column that is going to be a helper column it is just going to create a really clean formula within the Json and hopefully eliminate any errors going forward once we are finished then we can simply hide this column so that it's not visible in the front end but it is still working in the back end if you are not familiar with calculated columns within Microsoft lists then I would highly recommend checking out my other video linked here just so that you can see exactly what's happening here this is the formula that I have inserted into my today CI column so basically we are just taking the check-in date column and then subtracting today's date and then this is going to return a number based on how many days we are from the check-in day or the status column this is also defined as a calculated column and the data type or the output value is defined as a single line of text so this just allows us to have that booked coming soon or checked in text that is based on a calculation between these two columns this is the formula that I have put in to my status column and basically it's saying that if today CI value is less than 0 or negative then that pet has checked into the resort if it's greater than seven then they are booked and arriving at a later date and if it's neither of those then it means that that pet is going to be arriving within seven days which means that they are coming soon now I did just want to share a little tip when you are creating these calculated columns within Microsoft lists and you may notice that when you don't quite get the formula right then you're going to get this error and you're going to have to go back and it's just not really a user-friendly process alright so rather than troubleshooting your formula in the column creation I recommend exporting your list to a CSV file and then within Excel you can troubleshoot your formula there as it's much easier and you're not going to go back and forth between that warning once you've got it dialed you can just you know double check that it works on all of your columns and then when you go to create your column within Microsoft list you're just going to have to update this cell reference which is going to be the helper cell all right now we are on to the fun stuff so this is the Json that we are going to be inserting into our list to set up the color formatting don't worry I'm going to pop this in the comments below so it's going to be easily accessible for you and what we are going to cover in the next couple of steps is the creation of an if statement to go right here between these two brackets I did want to add a quick note to say that I am working on SharePoint version number two so this is going to be the Json that works for that version if this Json does not work for you then you might be on a different version if you aren't sure which version you are in SharePoint then you can just select any column and head on down to column settings and then format this column just ensure that you are on this format columns tab at the top then head on down to the advanced mode here we will find the standard Json formatting and you can see in mind that it says V2 so if yours also says V2 then you are on the same version as me otherwise if you're a V1 or something else and you're going to be on a different version of SharePoint and this Json formula is probably not going to work this is the Json if statement that I have generated yours will of course be tailored to your needs but we are going to go over the components together now if you think that this looks a little bit familiar you will see here that this is a nested if it is a very similar format to that status column if statement that we went over at the beginning of the video starting with the field names that I have bolded here these are the reference columns that we will use for the formatting there are a few key things that I wanted to highlight on the field name the field name is also known as the internal name it is different to the display name and is defined on column creation so when you create a column and Define a name that is when this field name is created so I'm going to show you a little bit more about this in just a second but when we insert the field names into the Json formula we just want to be sure that we include the brackets on the beginning and the end as well as that dollar sign at the beginning of the field name just how I have it here to find the field name we head on up to this gear icon go to list settings and then we scroll on down and find the column that we want to use in this example we are going to be inserting the today Ci or today check-in so if if we click on that then we will see up here in the URL at the very end it says field equals today CI so today CI is my field name in this case it also matches my display name but that is not always the case so it is best to just check this here first now I did want to share another tip with you so today is your lucky day and this one has to do with defining your field names within Microsoft lists and it is known as camel casing so let's take a look at what that means let's take the check-in date column for example so the display name is check-in date and when we go up to the field name we'll notice that it is check underscore x002d underscore n and the reason for this section in the middle here is because of the hyphen so when I created this column I did go check Dash in but but when you create columns going forward the best thing to do would be to do camel casings so Capital C or if you want to be lower at the beginning that's fine but then you can do capital I in the middle so that way you're not having any you know changes in your field names to accommodate four spaces or hyphens and then once you've created the column then you can just change it back to what you would like the display name to be so that it is easier to navigate your list all right and now on to Colors within the Json so I have highlighted some color hex codes here and when we add these hex codes to the Json we just need to be sure that we add the apostrophes at the beginning and end as well as that hash or pound signed at the beginning of the hex code an easy way to identify the hex code for the colors is to Define your colors within any Microsoft product really then hit this carrot drop down for colors and then go down to more colors and under the custom tab we have this hex code at the bottom our if statement is now complete and I have highlighted it at the top here now is all that we need to do is just pop it into this Json between these two brackets all right so we are just going to head to this status column column settings format this column ensure that you are on that format columns up at the top and then select advanced mode at the bottom I'm going to completely delete this base formula then I'm going to just Ctrl V the new formula and then here is that if statement that we are just going to copy our contents into so I'm just going to select right in between those two brackets Ctrl V and then now we can delete that space that was added for some reason and then just select the preview button and there you go now that Json formula has formatted the status column based on the check-in date with the help of the helper cell so I'm just going to click save you did it nice work woohoo all right so that wraps up this video and I do hope that I have helped you discover your inner nerd today and I also hope that I have helped you apply some formatting based on an if statement within Microsoft list thanks for noting out we'll see you again

Channel: Office Skills with Amy

Views: 2,967

Keywords: office skills, technology skills, how-to video, learn computer skills, Microsoft 365, Microsoft Office, 365 apps, Microsoft Teams, Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Microsoft OneNote, CRM, Lead Management, Administrative Skills, 2023, Computer Programs, How-to be efficient at work, How-to save time at work, Office Skills with Amy, OSWA, Microsoft Lists, Custom Date Format, IF Statement, Calculated Field, JSON, Sharepoint Version, Field Names, Camel Case

Id: 3h3-OWPeizw

Length: 9min 48sec (588 seconds)

Published: Tue Sep 12 2023