Excel Conditional Formatting for Dates in the Past/Future/Today

we're going to look at some tips and tricks for applying conditional formatting to dates as well just got a random list of dates and I'm going to select them all so I select the first cell ctrl shift down arrow key and then if I use ctrl backspace it takes me back to the top of my list so conditional formatting I'm just going to go to my home tab and I've got a conditional formatting button here and if I go to highlight cell rules I can see that I have a little option here called a date occurring so let's click on that and you see there's loads of useful options in this first drop-down list here so by default says yesterday so what it does is selects or highlights not selects but highlights or formats wherever yesterday's date appears in that list but I could also say today and if I had today's date and there somewhere and I do highlight today's date what about last week so those are all last week states so if you had a list of things to do you might for example say well what's coming up this week and it would highlight all the cells with dates in this week or you might say well what's coming up next week and there we are you've got all next week States formatted okay so lots of useful options in there and that was just merely selecting the cells that you want to apply the conditional formatting to and then going to conditional formatting button and going to highlight cell rules date occurring in this second example we've got a list of invoices and the due date for the invoice and we want to use conditional formatting to basically format any invoices that are overdue so the first step would be to select all of the invoices so if I select the first row and then I'm going to use a needle to our shortcut key ctrl shift down arrow key to select down to the last record then ctrl backspace to go back up to the top now I'm going to go to the conditional formatting button on the Home tab and this time I'm going to go down to new rule in this dialog box the new formatting rule dialog box I'm going to get into this last rule type use a formula to determine which cells to format and the formula that I'm going to use is basically going to evaluate whether the due date is less than today's date whenever you create a formula to determine which cells to format that that formula basically may must evaluate to true or false so that all those typing equals in to begin my formula and I'm going to say is this due date now you'll notice that when I click on that cell you makes it an absolute reference with the dollar before each part of the cell reference this formula is being created for d4 which is the active cell but will automatically get copied across all the other cells in my table my selection now what I'm going to need to do is fix part of this reference I need to say that the due date the thing that we're comparing is always in column E but with even different rows within economy obviously we're going to evaluate each date as we go copy the formula or copy the conditional formatting down this table so what I need to do is I need to fix the EE so retain the dollar before the EE but I need to make for relative and I can do that by using the shortcut f4 on my keyboard now if I press it once you can see that it makes the for fix so the dollar is before the fourth that's not what I want I'm going to press f4 a second time and I get a dollar before the e which is what I want now if the or f4 key is not quite working like mine is on my keyboard you can always just type in the dollar where it needs to appear within the cell address so what I'm going to say is this date less land and I'm going to use the function today so that's today open bracket close bracket and the reason I'm using that function is it will always return the current date within the formula so in other words the conditional formatting a bit will be kept up to date now I'm going to choose the format for this condition and I think what I do is I just darken the cell somewhat maybe I'll use that color there and let's make the font bold or something like that that's the color and let's say it's bold I should do if I click on OK and I click on OK again there we are it's very clearly shown which of those invoices are overdue so that's conditional formatting using a formula with a date function

Channel: Chester Tugwell

Views: 110,821

Rating: 4.7113404 out of 5

Keywords: microsoft excel, conditional formatting, dates, formula, excel change colour if date has passed, excel conditional formatting dates overdue, how do you get a cell in excel to change color when a date expires?, excel conditional formatting dates older than, conditional formatting based on date proximity, excel conditional format today's date

Id: BZ8SmUygBBs

Length: 5min 16sec (316 seconds)

Published: Fri Jan 19 2018