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.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast, episode 1269.
Highlight Today Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Had a great question day, from Jim.
Jim's in California, all those of Cleveland Indians fan.
Jim was trying to organize, it 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.
When I'm recording this and also when you're watching it and so we want to see we can write a formula that will identify all of the Tuesday dates, now.
Jim actually have real live dates out there in column A.
I just have text, Monday, Tuesday, Wednesday, 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 really, to show the current time and so it would never be exactly equal.
But, in this method.
We can 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.
If you're in Excel 2003.
You'll go to format conditional formatting and then change that first drop down from cell value is to formula, is in this case where in it say, use formula to describe which cells to format and I...
Look at this, I see that A2 is the current cell that has to be the one, that we write the formula for.
They will apply it to all cells and what we're going to do is. We say equal (=) the Text of today, Open parenthesis, close parenthesis, and then in quotes "DDDD",that's going to spell out the day of the week.
And so, we have that test want to see if that's equal to (=) Dollar sign ($B2).
That's really important the dollar sign before the B.
That says even for Columns.
Actually no, =$A2.
Even for columns B and C.
I always look back to column A that the fact that we have no dollar sign before the 2 and act as cell to says, 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...
Well, if you completely honest with you as a pretty hideous format, but will replicate.
What he had with a yellow, bold italic on top red format.
Click [ OK ].
Alright! So, there we go, 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.
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 Three-letter abbreviations.
You can actually, get away with just DDDD.
So, that will be MON.
Lots of different ways to tweak this, to make it match your data Hey! I want to thank you for sending question in, and thank you for stopping by.
We’ll see you next time for another netcast, from MrExcel.
Learn Excel from MrExcel Podcast, episode 1269.
Highlight Today Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Had a great question day, from Jim.
Jim's in California, all those of Cleveland Indians fan.
Jim was trying to organize, it 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.
When I'm recording this and also when you're watching it and so we want to see we can write a formula that will identify all of the Tuesday dates, now.
Jim actually have real live dates out there in column A.
I just have text, Monday, Tuesday, Wednesday, 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 really, to show the current time and so it would never be exactly equal.
But, in this method.
We can 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.
If you're in Excel 2003.
You'll go to format conditional formatting and then change that first drop down from cell value is to formula, is in this case where in it say, use formula to describe which cells to format and I...
Look at this, I see that A2 is the current cell that has to be the one, that we write the formula for.
They will apply it to all cells and what we're going to do is. We say equal (=) the Text of today, Open parenthesis, close parenthesis, and then in quotes "DDDD",that's going to spell out the day of the week.
And so, we have that test want to see if that's equal to (=) Dollar sign ($B2).
That's really important the dollar sign before the B.
That says even for Columns.
Actually no, =$A2.
Even for columns B and C.
I always look back to column A that the fact that we have no dollar sign before the 2 and act as cell to says, 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...
Well, if you completely honest with you as a pretty hideous format, but will replicate.
What he had with a yellow, bold italic on top red format.
Click [ OK ].
Alright! So, there we go, 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.
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 Three-letter abbreviations.
You can actually, get away with just DDDD.
So, that will be MON.
Lots of different ways to tweak this, to make it match your data Hey! I want to thank you for sending question in, and thank you for stopping by.
We’ll see you next time for another netcast, from MrExcel.
