Ross has a data set in Excel. He wants to highlight the entire row if the date is more than 60 days old. Today's episode shows how easy it would be to highlight the date cell and the formula necessary to highlight the entire row using Conditional Formatting.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1906.
Highlight Entire Row if Date is Over 60 Days Old.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Ross from California: I have a date, say June 11th 2014.
I need to highlight the row, when it gets to 60 days past this date.
How can I do that?
Boy, if it wasn't for the row, this would be relatively easy.
But first let's talk about a great little function called =TODAY().
I bet you thought I was going to say =NOW, everyone seems to know NOW, which gives you the date and the time, TODAY though just gives you the date, it doesn't give you the time portion of that.
And that function is a great function, because it will recalculate.
I'm recording this on August 11th, but if I came in and open this workbook tomorrow, that date would be August 12th.
So the TODAY function, no arguments there.
Let's first just say, if we wanted to highlight the date in column C, if it's more than 60 days past that date, we would select all the data, so Ctrl+Shift+Down arrow and in here, under Conditional Formatting.
And then I highlight cells that are Less Than, because we're looking for things that are more than 60 days, so less than a certain date and that date is going to be =TODAY()-60, alright.
And so now we just have: the things that are older than June 11th are being highlighted and right now they do it in light red fill with dark red text.
You can open this drop down and choose any of the various colors, or you can go to Custom Format.
And we'll take a look at that when we have to highlight the whole row.
But for right now you can see how easy that would be, if you just had to highlight the one cell.
But, if we want to highlight the whole row.
All right, so that's a little bit more difficult.
We have to figure out which row is the first row in our dataset, in this case it's row 2, and which column has the dates, in this case it's column C. And your data, it's probably going to be somewhere else, but we're going to select all the data from here down, so Ctrl+Shift+Right arrow, Ctrl+shift+Down arrow.
And then we're going to go to Conditional Formatting and New Rule.
See, these items, they have… they don't have the one powerful one, which is called “Use a formula to determine which cells to format”.
And we have to write a formula that is relevant to cell A2, but would work in any of the cells.
So we're going to say =C2.
And I have to put a dollar sign, carefully in there, I don't want to lock down the row, because I need the reference to change, but I do need to lock down the column C, so I can press the F4 key.
Once – will lock them both down, twice – will lock down just the row, third time though – will lock down just the column, that's what I need.
So if the date and C2 is less than(=$C2<) and I'm going to put parentheses here, TODAY, open and close parentheses, -60, all right (=$C2<(TODAY()-60)).
So we take today's date, August 11th, we subtract 60 days from it, so we'll get back to roughly June 11th and then, if the date there is older than June 11th, so it's more than 60 days, that it’s been in the database, then we want to highlight the whole row.
And here, click the Format button and we have all kinds of different things we can do.
We can change the font color, we can change the fill color, maybe we'll just highlight everything in a, well, kind of a… some sort of red.
And then change the font color to be white or something like that, you can also change the border or even the number format.
So we'll click OK and then click OK, and we'll see anything that is more than 60 days ago.
The entire row is now highlighted in red, so very easy to spot those stale items, that we need to make sure get taken care of, or checked upon, or whatever.
Now you can imagine, if you have a different set of rules, like if you want to see everything that's, you know, more than 30 days old, or coming up in the next three days, you can just change that formula.
The really important thing there is to lock down the column that has the date, all right.
So, you know, as you… as you think about that, we always want to be looking, no matter which cell in this range, that we're evaluating from.
We're always looking back to column C, so putting that dollar sign just before the C, not before the 2 - is the important part.
Well, I want to thank Ross for sending that question in and I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1906.
Highlight Entire Row if Date is Over 60 Days Old.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Ross from California: I have a date, say June 11th 2014.
I need to highlight the row, when it gets to 60 days past this date.
How can I do that?
Boy, if it wasn't for the row, this would be relatively easy.
But first let's talk about a great little function called =TODAY().
I bet you thought I was going to say =NOW, everyone seems to know NOW, which gives you the date and the time, TODAY though just gives you the date, it doesn't give you the time portion of that.
And that function is a great function, because it will recalculate.
I'm recording this on August 11th, but if I came in and open this workbook tomorrow, that date would be August 12th.
So the TODAY function, no arguments there.
Let's first just say, if we wanted to highlight the date in column C, if it's more than 60 days past that date, we would select all the data, so Ctrl+Shift+Down arrow and in here, under Conditional Formatting.
And then I highlight cells that are Less Than, because we're looking for things that are more than 60 days, so less than a certain date and that date is going to be =TODAY()-60, alright.
And so now we just have: the things that are older than June 11th are being highlighted and right now they do it in light red fill with dark red text.
You can open this drop down and choose any of the various colors, or you can go to Custom Format.
And we'll take a look at that when we have to highlight the whole row.
But for right now you can see how easy that would be, if you just had to highlight the one cell.
But, if we want to highlight the whole row.
All right, so that's a little bit more difficult.
We have to figure out which row is the first row in our dataset, in this case it's row 2, and which column has the dates, in this case it's column C. And your data, it's probably going to be somewhere else, but we're going to select all the data from here down, so Ctrl+Shift+Right arrow, Ctrl+shift+Down arrow.
And then we're going to go to Conditional Formatting and New Rule.
See, these items, they have… they don't have the one powerful one, which is called “Use a formula to determine which cells to format”.
And we have to write a formula that is relevant to cell A2, but would work in any of the cells.
So we're going to say =C2.
And I have to put a dollar sign, carefully in there, I don't want to lock down the row, because I need the reference to change, but I do need to lock down the column C, so I can press the F4 key.
Once – will lock them both down, twice – will lock down just the row, third time though – will lock down just the column, that's what I need.
So if the date and C2 is less than(=$C2<) and I'm going to put parentheses here, TODAY, open and close parentheses, -60, all right (=$C2<(TODAY()-60)).
So we take today's date, August 11th, we subtract 60 days from it, so we'll get back to roughly June 11th and then, if the date there is older than June 11th, so it's more than 60 days, that it’s been in the database, then we want to highlight the whole row.
And here, click the Format button and we have all kinds of different things we can do.
We can change the font color, we can change the fill color, maybe we'll just highlight everything in a, well, kind of a… some sort of red.
And then change the font color to be white or something like that, you can also change the border or even the number format.
So we'll click OK and then click OK, and we'll see anything that is more than 60 days ago.
The entire row is now highlighted in red, so very easy to spot those stale items, that we need to make sure get taken care of, or checked upon, or whatever.
Now you can imagine, if you have a different set of rules, like if you want to see everything that's, you know, more than 30 days old, or coming up in the next three days, you can just change that formula.
The really important thing there is to lock down the column that has the date, all right.
So, you know, as you… as you think about that, we always want to be looking, no matter which cell in this range, that we're evaluating from.
We're always looking back to column C, so putting that dollar sign just before the C, not before the 2 - is the important part.
Well, I want to thank Ross for sending that question in and I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
