Highlight Today: Podcast #1269

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Oct 20, 2010.
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.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top