 Hello everyone and welcome back to another video. In this week's video we're going to be looking at conditional formatting But how to use formulas in order to generate the conditional formatting Rules or criteria should we say? For that we've got a table you can see on the left-hand side here So we've created a scenario of a movie rental So you can see what a list of movie titles and you can see the start date Someone obviously took the start at the rental of the film and then the end date And we want to compare this end date to today's date what you can see here in row six So you can identify all of those films that are currently Overdue so they're either sort of the date is past when they meant to bring it back And we want to highlight the ones that we need to now chase these people up about So in order to do so we're going to use conditional formatting But ultimately we're going to be using a simple formula to identify true or false What ones are either overdue or actually they're still fine to continue When using formulas with conditional formatting, there is a couple of rules that we need to abide by This is ultimately that the formula we use must evaluate to either a true or false result or the numeric equivalence, which is one and zero or Ultimately if we're going to be using a formula what evaluates to a number If it generates a positive or a negative number It will be treated as true and if it evaluates to zero then it will be treated as false So it's just another little criteria just to let you know So if it's going to generate a number from your formula if it's a positive or a negative number So basically anything other than zero it will be true, but if it's zero it will be evaluated to false and then therefore Perform the criteria that you require First thing we're going to do is just enter our formula here into column E Just so you can see what it looks like and make sure the formula is working before we add it to the conditional formatting All we're going to do is simply do equals End date is less than or equal to two days date And that is the formula one thing we need to do is just check our references here So we just need to do an absolute cell reference to our today's date in row six here Just to make sure that it's not going to be changed Obviously as you drag this formula down to do so all we need to do is just hit F4 once selected that C6 for in our example here Hit enter and it carries on as normal So we'd copy that down and we can see we've got a range of true and false answers So we just can check that yes. So the ones we've got false It's given us end dates that are in excess of today's date and therefore they haven't yet exceeded their end date So they're still fine and we don't need to worry about chasing any of those people So we can jump straight in to the conditional formatting now that we know the formula is working as intended To do so we need to and this is a key part to remember Because we want to apply our formatting to the entire row and so that these ones here where it says True we know that these overdue. We don't want to just highlight this one date We want to highlight all that reference all that row relative to that date So that we've got a clear indication of all the information that is now overdue to add the conditional formatting What we now need to do is make sure we selected the whole Table or whole range we wish to apply formatting to so in this example You can see up pretty much selected the whole table minus the headings at the top there We're then going to go into conditional formatting and we're going to go into new rule And then use a formula to determine which cells to format So in in here, this is where I can now enter the formula over quiet So for us, it's going to be equals and my sub first cell is going to be D 9 I want to see where that is less than or equal to our today's date which is in C 6 So this is obviously the basic we've put in there But what we need to now do is ensure we've got correct Absolute and relative cell references applied to our formula and by that I mean we need to ensure that C 6 that we're going to always be looking at this today's date So in order to do so, we just need to highlight and do f4 and You can see by hitting it once it gives us dollar signs to make sure we're absolutely on the column and the row reference So that's great We always want to be referencing this in regards to comparing against today's date But in regards to D 9, we always want to be obviously looking at this column of D So yes, we want to lock that but we don't want to lock the row So as this formula comes down, we obviously want to be looking at each row independently So that it can do the evaluation on if that end day is Less than or greater than then today's date. So for this one, all we need to do is F4 once twice gets you on to the row and then the third time means we're then locking that column D And then that is our formula that we require for this conditional formatting The last thing to do is just add a color of choice So we can format based on when this evaluates and then we can go into maybe that pink there Select okay, we get back to this page here. So we can make sure all validating happy Select okay one more time and you can see we've now got the formatting applied to those rows Where the movie title is now overdue and we can quickly validate that against this simple formula We put in in the first place. So all the ones will have evaluated to true We can see yes that day is indeed true because it is less than or equal to today's date And we can just cast the eye down there and we can see that all of these are actually past dates Apart from this one actually in row 15 We can actually see that the 12th of June. So it's actually today's date So this one is you could say it's overdue because today has been reached But you might also want to say okay. Well, it should be arriving back today So it's not quite overdue in that sense Last thing to remember to do is just remove this column E here just so that we you know Just tidy up the page and then we can go we've got our nice tidy and completed table So I hope you enjoyed that video and it gave you an insight into using formulas with your conditional formatting If you do have any questions at all, please just leave a comment below this video And I'll get back to you as soon as I can Also as an addition We've got some films there if you've seen one of these films and you suddenly think I really like that film I'm not gonna go watch it because of this video again Why not drop us a comment? It'd be great to hear if you actually have been inspired to watch one of these films based on today's video Lastly if you did enjoy today's video Please don't forget to give the video a like and also if you are new or use as your repeat viewer of our channel Please do remember to subscribe to the channel and hit that bell notification button so you're notified of all of our future videos Thank you very much for watching and I'll see you in the next video Before you go, don't forget to check out the other videos on our channel Or you'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information And obviously as always don't forget to subscribe and hit that bell notification button