 Hello everyone and welcome back to another video in today's video I'm going to be showing you how you can create the simple task tracker to call it that Using conditional formatting that allows us to apply logic when we compare today's date to the date for a given deadline And you can see we've got one or four scenarios here based on our color formatting We've got deadline being today or missed. It's going to be red If our deadline date is within the five days of today's date Then it's going to go to amber or orange. If however, we've got longer than that So we've got six plus days until our deadline then it's green because it's all okay Or if our task has been flagged complete indicated by a date in our completed column Then we're going to highlight it in blue So what we'll do is we'll go back to a blank document and we'll go through the stages of how we put this together Okay, so jumping straight in you can see I've now removed the conditional formatting from our deadline column So we'll leave all the dates exactly the same because it just helps as we go back through this But what we'll now do is one by one apply the conditional formatting to allow us to achieve our desired result So the first thing I'm going to do is highlight all of the cells in which we want to apply conditional formatting to It's just easy for the scenario. Obviously There are different ways of doing this But I'm just going to highlight all the ones that we have in question here And the first thing I want to do is go into conditional formatting and I'm going to go into manage rules I always just go in this way You can obviously go into other various methods that you choose or the desire But I'm going to go in this way just to show you this Solution so first thing we want to do we can see what no rules at all applied to our range So we just need to go into new rule And we're going to use our user formula to determine which cells to format So we're always going to be using this bottom option here And simply all we need to do here is put a very basic formula to help indicate to us or to help Apply the logic. So the first one we're going to do is that blue color So how do we know when a task is complete and when it is complete? We want to highlight the cell Or I say the cell of the deadline date. We want it to be blue All we simply need to do here is go equals And for us we want to indicate when there is a completed date And you'll see what I'm going to do is now enter dates all applying to row two But because our range is selected We've set that whole range it's going to apply to all ourselves So that'll probably come more make more sense as we progress through this So the first thing we'll do is go d2 greater than zero what basically means this is not blank So it's going to be greater than zero And then once I've done that go format and select our blue color what happens to already be in our palette here Click okay, and you can see it's now asked us to confirm okay once again And now we've got our first rule so we can see the formula is if d2 Is greater than zero so d2 hit the temp for the January is greater than zero Then we want the color blue to be applied to the corresponding cell in column c So if we click okay You can see that that now has applied that logic to all of ourselves And if we were to go into maybe row five and delete that date You can see that obviously by there being no date there It's automatically removed our formatting for us And likewise if you go into one of these other ones maybe here one in the future If we now put a date of the third of jane A third of april, sorry You can see it's now going to apply that logic to that cell there So we can say that first our completed option here has now been ticked off So let's progress through and look at some of the other ones So I'm just going to select this same range again conditional formatting manage rules We can see our existing rule sits there, but we're just now going to now go and do another rule So the next one is by no means again the hardest thing to look at or to hardest formula to do But it's going to be the most tricky one out of our Solution here today. So for this one, we want it to be two scenarios to apply So we're going to be using an and function So for that we're going to type equals a and d and open our brackets And the first part of this is we want to ensure that Today is greater than or equal to the deadline date And the reason for doing that is obviously say we've got a deadline here Today's date is the third of april. So we're going for row six here for this example If today's day is the same as the deadline date So the third of april or today's date has passed that date So it's now is maybe the fourth of april We want this to be red And because obviously it means that we've obviously gone past our deadline But however, we've already put a rule here that says if this completed date has been populated Then we wanted to be blue. So the reason for doing our double logic Is because the first part we want to say is if You know today's day is either the deadline day or after the deadline date But the completed day is blank Then we want it to be red because obviously if this complete column is not blank It's got a date then we know the task is completed and therefore we we don't we only want to do We wouldn't want to then make it red. So for us, all we're going to simply do here is type equals today Is a greater than or equal to c2 so the deadline date And also and I'm now separating this logic out by column So the first part is this is the first scenario today is greater than or equal to c2 or deadline date And then by going into after our comma, we're now applying a second scenario or piece of criteria that also must be true And for that it's going to be and d2 Is equal to blank and again There's probably different ways that you can do that or there are different ways that you can do this This is just the way I've gone for this scenario here. So Boyle means feel free to reflect play around with different ways of doing this Uh, so in this scenario if obviously the deadline date is today or today has passed the deadline date And the completed column is still blank. Then obviously we want to make this red So I'm going to go select this red click okay And do okay once again and you can now see we've got this red scenario here Let's click okay, and we can now see that that has done just that Any day was obviously less than today's date But hasn't got a completed date has now been highlighted red If I was to move this now to say the fourth of april so tomorrow At the point of record in this video, then you can see obviously that scenario is no longer applicable And it's now got no fill for just your control C go back to that day You can see it's now gone back to that Alternatively if I go in here and put a completed date. So let's say the third of april 2021 You can see that it's now fallen into that first scenario where we know the task is completed And therefore we want this deadline date to show as blue. So we know so far both of those are working as intended So we'll now quickly do the next and last two. So go manage rules and create another rule Use a formula to determine which cells to format So for this one, we're going to do equals I'm going to open brackets here. So c2 so deadline day minus today And we've got to do another do our brackets when we're using the today function cross brackets So c2 minus today, we obviously it will do the result to find out what the difference is So if that is less than the number six, we want this to be orange So we're going to go into orange and what that indicates to us is obviously the deadline day Minus today's date So if the deadline date was in the future say and we know that there's a number of days So it could be one two three or five in this scenario Then we want to show this amber color because it's just indicating to us that yes We haven't reached our deadline, but you know, we're getting close So therefore the amber color is telling us that you're getting close to this deadline Therefore you need to start doing something about it And then we can click okay for that one and then the last one we want here is you know When everything's all well and good. We want it to be green So we're looking at deadlines that might be way into the future So it's exactly the same as that last function or that last formula in many ways So we're going to equals open brackets c2 minus today. So obviously this part is exactly the same again But if the result of c2 minus today is greater than five So six plus days Then we want this to be green because we know our deadline is way off into the future So we don't need to be worrying about that Let's just click okay, and we can now see our entire four Four rules to use the right terminology here Click okay, and you can see that those rules have now been applied to our data set And as you can see at first glance, it's not really following the scenarios that we want it to look at because obviously Some criteria might overlap in this scenario So what we're going to do is highlight all ourselves once again Go back into our condition or format into manage rules And we're now going to just put this into a more logical order So what we'll say is move this one up to the top Because obviously if this one is true, so d2 has got a completed date We just want that to be top of priority. So that's the first thing we want to check for And what we're now going to do is put these rules into that same order of priority So that they get checked in the correct order So the first thing we want to do is to make sure obviously if they if they're complete We highlight them complete The next one I'm going to do is just move our red one up next So if it's not complete we want to then test for this So we'll is today, you know greater than or equal to the deadline day and that is blank So we can test if it's red if that's not correct Then the next thing we want to do is to go into this next solution Or next rule to say, okay, well, I want you to now test for this Are we within, you know five days of the deadline? And lastly if we're not in any of these scenarios Then chances are we're going to have this scenario at the bottom So if we now click okay on this You can now see that it's put our logic into the correct order So anything with a completed date is now mark blue And then anything else thereafter has had the correct logic applied to it So for the deadline we can see the first of April of even the third of April Because obviously the date has been passed or is the same as today's date We're getting that red but soon as we put a completed date in here We can see that obviously it corrects it for us. So our conditional formatting is now working as intended So I hope you enjoyed that video. I sort of cut off the end there But I didn't want this to run on too long And I hope you have enjoyed that video and it's allowed you to understand How you can use conditional formatting to create these rules and this tracker in excel You might want to just go back through the video and pause at that part Where we looked at all the rules if you just want to copy them down So I think that covers off everything I wanted to look at in today's video And I hope that's giving you an introduction on how you can use conditional formatting To create this task tracker for you in excel And obviously most importantly it just allows you to identify those most important tasks and the ones that you want to look at in priority order You may wish to go back through this video pause at the section where we had all our completed rules in front of this So you can just copy them down and apply them to your logic as well If you did enjoy today's video, please make sure you do hit that like button It's not only greatly appreciated by me and obviously shows me the videos that you'd like to see more of But it does also help that all important youtube algorithm to make sure that more people are able to find this video And if this is your first time finding one of our videos or you've watched them in the past Please don't forget to hit that subscribe button and also that bell notification button That way you'll be notified of all of our future videos as and when they come out So lastly, thank you again for watching and we'll see you in the next video