Today's Duel looks at a question from YouTube viewer Shahmeer: "How can I highlight any date after 4 days - only if the next Cell has no data?" Follow along with Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen in Episode #1717 as they work out differing solutions to this question.
Dueling Excel Podcast #125...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #125...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Alright welcome back, it's time for another Dueling Excel podcast. I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun, this is our episode 125 - Highlight Open Items After 4 Days!
Today's question sent in by Shahmeer, and hey, this is funny. On the MrExcel podcast this week Shahmeer also sent in how do we globally change all the word "fox" in all cells to red. Shahmeer's getting a 2 for this week "How can I highlight any date after 4 days? But, if in the next column I have put any date or text, there's no need to highlight after 4 days." So here's some sample data, we have, you know, dates, this must be like the date that something arrived. And then if- Ah, they must be invoices. Alright, so if something was paid or phoned in, we don't need to highlight it. So I'm going to do this, I'm going to select all these cells, I'm going to set up Conditional Formatting, that's Alt O D. We're going to create a New Rule, and we're going to use a formula to determine which cells to format. And this formula has to be written as if we're talking about the top-left hand corner cell in the selection, A3.
So we have two things that have to happen, so =AND, start with that, the first thing, is we're going to compare TODAY(), that's will return today's date, -A3, and see if that's >4. If that's greater than 4 then our first condition is TRUE, and I know that I'm recording this on May 22nd, even though it's going to be put out on, what, May 24th, I'm working a couple of days ahead. Alright so that's our first condition, then the second condition is, if there's something over here in B3, then we don't want it to be TRUE, so I'm going to ask for ISBLANK of B3. And final )). Too bad they don't give us color-coding of the parentheses in here, I've gotten used to looking for that last parenthesis, but it looks like we're balanced here. And then Format, just go in and choose some sort of a format, let's mark those in yellow, and click OK, alright.
So, again, the conditional formatting formula has to be talking about the top-left hand corner cell, which normally is the active cell in your data. Click OK, click OK. And you'll see that any of these old items are marked the new items, this was yesterday or two days ago. And then if, let's say we call, and we get this one Paid, as soon as I enter Paid, the second condition no longer is TRUE, and the conditional formatting goes away.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel! Hey, I love this formula here, and here's the crazy thing, my formula is going to be really similar to this but totally different. Now actually, I kind of like the way you did this, this is not the way I did it, you took TODAY which is a serial number, minus the date there. If I hit F9, you can see- Oh yeah, that tells you how many days ago this date was. And then you just compare it to the 4, Ctrl+Z. And you use the ISBLANK that checks if that's empty! Alright, I'm going to come over to this sheet "Mike".
Now, instead of highlighting in the cells and using the dialog box to create my formula, I'm going to come over here to the cells.
You know, I always have a hard time creating this, and it's easier to see and make sure it's working when you're in the cell. Alright, similar formula, I'm going to use the AND, but check this out, I thought of this a totally different way. I said "Hey, is the serial number there <= ?" And then I did TODAY -4.
So I actually took TODAY, subtracted 4, and then compared the 2. Now, I like MrExcel's way of doing it because he didn't have to type that equal sign. Now another thing about this one, I'm not going to put parentheses around this, because that math operator will calculate before the comparative operators.
Comparative operators happen at the bottom of Excel's order of operations. So that will check the date, and then comma, the second logical test, instead of using this block, I'm going to say "Hey, is that cell =""?" Now that's a null text string, but that will check to see if the cell is empty. Alright, so when I Ctrl+Enter, it will give me the same pattern of TRUEs and FALSEs.
Now I can copy from here and highlight, but check this out, that active cell, you can have the active cell anywhere, but you better build a formula from the point of view of that active cell. So I'm actually going to scoop this out from the middle, like I'd never do this when I'm building something, but let's just check it out and see if it works. So if the active cell is right there, Alt O D, Alt N, arrow arrow arrow, TAB, and Ctrl+V.
So it doesn't matter, as long as that formula is from the point of view of that cell. I add the same formatting as MrExcel, click OK, click OK, so we get our conditional format.
If I come up here, (typing), so it looks like it's working. Now, one other thing about the two things, MrExcel used ISBLANK, I used equals "", they both are awesome, but they do different things. Sometimes you want one, sometimes you want the other. ISBLANK is cool because it actually checks for only an empty cell.
If you have a formula delivering a null text string, it isn't empty, even though it's a text string with zero length, it's still something, that cell is not empty. Whereas this is asking a question "Are you an empty cell, or are you a null text string?" So that's a difference between those. Alright, throwback to MrExcel!
Bill: Alright Mike, you're right, exactly the same and completely different. You're right, I didn't need parentheses there, but my rule of thumb is, if you're not sure if you need parentheses or not, and you can't remember the order of operations, it's always better to put them in. It doesn't hurt to have them there, if you are too lazy like I was this morning to look them up. ISBLANK or ="", you know, both of those are going to fail when someone comes along and clears out the cell by just typing a space and entering it. You'd be really careful that, you know, you always press the Delete key to clear that cell.
Alright well hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun!
Today's question sent in by Shahmeer, and hey, this is funny. On the MrExcel podcast this week Shahmeer also sent in how do we globally change all the word "fox" in all cells to red. Shahmeer's getting a 2 for this week "How can I highlight any date after 4 days? But, if in the next column I have put any date or text, there's no need to highlight after 4 days." So here's some sample data, we have, you know, dates, this must be like the date that something arrived. And then if- Ah, they must be invoices. Alright, so if something was paid or phoned in, we don't need to highlight it. So I'm going to do this, I'm going to select all these cells, I'm going to set up Conditional Formatting, that's Alt O D. We're going to create a New Rule, and we're going to use a formula to determine which cells to format. And this formula has to be written as if we're talking about the top-left hand corner cell in the selection, A3.
So we have two things that have to happen, so =AND, start with that, the first thing, is we're going to compare TODAY(), that's will return today's date, -A3, and see if that's >4. If that's greater than 4 then our first condition is TRUE, and I know that I'm recording this on May 22nd, even though it's going to be put out on, what, May 24th, I'm working a couple of days ahead. Alright so that's our first condition, then the second condition is, if there's something over here in B3, then we don't want it to be TRUE, so I'm going to ask for ISBLANK of B3. And final )). Too bad they don't give us color-coding of the parentheses in here, I've gotten used to looking for that last parenthesis, but it looks like we're balanced here. And then Format, just go in and choose some sort of a format, let's mark those in yellow, and click OK, alright.
So, again, the conditional formatting formula has to be talking about the top-left hand corner cell, which normally is the active cell in your data. Click OK, click OK. And you'll see that any of these old items are marked the new items, this was yesterday or two days ago. And then if, let's say we call, and we get this one Paid, as soon as I enter Paid, the second condition no longer is TRUE, and the conditional formatting goes away.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel! Hey, I love this formula here, and here's the crazy thing, my formula is going to be really similar to this but totally different. Now actually, I kind of like the way you did this, this is not the way I did it, you took TODAY which is a serial number, minus the date there. If I hit F9, you can see- Oh yeah, that tells you how many days ago this date was. And then you just compare it to the 4, Ctrl+Z. And you use the ISBLANK that checks if that's empty! Alright, I'm going to come over to this sheet "Mike".
Now, instead of highlighting in the cells and using the dialog box to create my formula, I'm going to come over here to the cells.
You know, I always have a hard time creating this, and it's easier to see and make sure it's working when you're in the cell. Alright, similar formula, I'm going to use the AND, but check this out, I thought of this a totally different way. I said "Hey, is the serial number there <= ?" And then I did TODAY -4.
So I actually took TODAY, subtracted 4, and then compared the 2. Now, I like MrExcel's way of doing it because he didn't have to type that equal sign. Now another thing about this one, I'm not going to put parentheses around this, because that math operator will calculate before the comparative operators.
Comparative operators happen at the bottom of Excel's order of operations. So that will check the date, and then comma, the second logical test, instead of using this block, I'm going to say "Hey, is that cell =""?" Now that's a null text string, but that will check to see if the cell is empty. Alright, so when I Ctrl+Enter, it will give me the same pattern of TRUEs and FALSEs.
Now I can copy from here and highlight, but check this out, that active cell, you can have the active cell anywhere, but you better build a formula from the point of view of that active cell. So I'm actually going to scoop this out from the middle, like I'd never do this when I'm building something, but let's just check it out and see if it works. So if the active cell is right there, Alt O D, Alt N, arrow arrow arrow, TAB, and Ctrl+V.
So it doesn't matter, as long as that formula is from the point of view of that cell. I add the same formatting as MrExcel, click OK, click OK, so we get our conditional format.
If I come up here, (typing), so it looks like it's working. Now, one other thing about the two things, MrExcel used ISBLANK, I used equals "", they both are awesome, but they do different things. Sometimes you want one, sometimes you want the other. ISBLANK is cool because it actually checks for only an empty cell.
If you have a formula delivering a null text string, it isn't empty, even though it's a text string with zero length, it's still something, that cell is not empty. Whereas this is asking a question "Are you an empty cell, or are you a null text string?" So that's a difference between those. Alright, throwback to MrExcel!
Bill: Alright Mike, you're right, exactly the same and completely different. You're right, I didn't need parentheses there, but my rule of thumb is, if you're not sure if you need parentheses or not, and you can't remember the order of operations, it's always better to put them in. It doesn't hurt to have them there, if you are too lazy like I was this morning to look them up. ISBLANK or ="", you know, both of those are going to fail when someone comes along and clears out the cell by just typing a space and entering it. You'd be really careful that, you know, you always press the Delete key to clear that cell.
Alright well hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun!
