MrExcel's Learn Excel #1007 - Formula Conditional Formatt...

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 May 5, 2009.
William asks how to base his conditional formats on a start date in column A and a duration in column B. This calls for the Formula version of conditional formatting. Episode 1007 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically we start out with massive amounts of data.
So how we're gonna analyze as well lets fire up a pivot table.
Lets see if you can solve this problem Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by William.
William has a question. He says he wants to use conditional formatting to highlight rows based on a start date in column A and the duration in Column B.
And what I'm guessing is he's looking for projects that are past due or something like that.
So before we even get into conditional formatting I want to just solve this in the spreadsheet.
I'm going to put a formula up here =TODAY which gives us today's date ofcourse and then I want to see if it's past due, so to figure out when it's due, we would take A2 + B2 and that gives us the due date and then to see if that's past due, I would use a formula saying is it < today, the TODAY function that we used up there in E1 and just coming down here.
So we have March 7 + 13 days, certainly less than a 5th, so that is past due.
you see we get a nice little range of Trues and Falses there now.
I'm not going to ultimately use that in the conditional formatting.
I just wanted to get my head wrapped around exactly how this is going to work.
Conditional formatting needs a little bit of dollar signs.
So we have a nice formula there = ($A2+$B2)<TODAY() and copy that whole thing to the clipboard.
Ctrl C and then press escape. okay, we can actually get rid of column C. Now. Here's what we're going to do. I'm going to start in cell A2 and select all of my rows. We're going to go to home "Conditional Formatting" "New Rule" and say that we're going to use a formula to determine which cells to format.
Now real important.
You want to look here in the name box.
This formula has to be referring to the current cell so in this case A2.
Want to press Ctrl+V and make sure that this is going to work.
It's always looking at column A + always looking at column B, seeing if it's less than today.
We'll format it. I'll choose a nice pattern like red or something like that maybe with a font color of white and may be Bold.
Click ok Click ok and bam. Just like that.
Anything that is past due changes to red now, let's change one of these. Let's come back and change this to 1/1/ 2009 and this is a start date.
So now it's past due and going the other way, if I was say that somebody starts 6/1/2009, clearly that one's not past due so it instantly updates.
Now for those of you in excel 2003. This is a relatively different. Let's take a quick look we go to "Format" "Conditional Formatting" and it doesn't appear to offer the "Formula Is" but you have to open this drop-down and choose "Formula is" and then we'd paste the same formula in there that we did in excel 2007.
So it's still possible in the old excel just just a little bit more hidden.
Want to thank William for sending in that question. Want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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