Jim from California is trying to organize his kid's home chores list. He was trying to mark the current weekday's chores with conditional formatting and a cell that contained =NOW(). In Episode 1269, Bill shows us a method to solve this problem.
Mr. Excel - Kid's Home Chores List Welcome back to the Mr. Excel netcast. I’m Bill Jelen. I got a great question today from Jim. Jim’s in California, although he’s a Cleveland Indians fan. Jim was trying to organize what looked like household chores. He had days of the week and the task and he wanted to see if there was some way using equal now to highlight the chores for today. Okay, so today is Tuesday. It's when I’m recording this and also when you’re watching it. And so we want to see if we can write a formula that will identify all of the Tuesday dates. Now Jim actually had real live dates under column A. I just have tasks Monday, Tuesday, Wednesdays. So for Jim, we’re going to have to use not now, equal now, open parenthesis, close parenthesis, but equal today. The difference, both will return today’s date but now changes every minute or every second to show the current time. And so, it would never be exactly equal. But in this method, we could actually use either now or today, either one would work. So we’re going to go to Home, conditional formatting and create a new rule. Now if you’re in Excel 2003, you’ll got to format, conditional formatting, and then change that first drop down from cell values is to formula is. In this case, we’re going to say use formula to describe which cells to format. And look at this, I see that A2 is the correct cell that has to be the one that we’ll write the formula for. Then we’ll apply it to all cells. And what we’re going to do is we’re going to say equal the task of today, open parenthesis and closed parenthesis, and then in quotes “DDDD”. That’s going to spell out the day of the week, the weekday. And so we have that text. We want to see if that’s equal to $B2. Now that’s really important, the dollar sign before the B. That says even for columns – actually no, equal, dollar sign, A2. Even for columns B and C, I always want to look back to column A. That the fact that we have no dollar sign before the 2 and the active cell is 2 so we’re always going to look in the current row, column A of the current row, and see if that’s equal to the text over there in A2. How we format it, I don’t care. You can use whatever you want. Jim actually had – to be completely honest with you, it’s a pretty hideous format but we’ll replicate what he had with the yellow bold italic on top of a red format. Click okay and we got everything on Tuesday is highlighted. Now tomorrow, when we open this up, everything on Wednesday will be highlighted. So again, the trick there is to write a formula that will check to see if the text of today, that’s going to return the weekday is equal to that value out there in A2. Now, we had real dates out there in A2 then we would use the text of A2 with the same format. If we just had a three-letter abbreviations, you can actually get away with just DDD so that will be MON. There are lots of different ways to tweak this to make it match your data.