MrExcel's Learn Excel #551 - Red-Flag Dates

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 Jul 20, 2009.
Van writes in with a suggestion about podcast 539. In that podcast, we were trying to calculate which customers had their birthday coming up soon. Van suggests using conditional formatting and the TODAY() function to flag the customers to whom you need to send birthday cards. Episode 551 shows you how.

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


Transcript of the video:
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Today we got a nice email from Van, Van was talking about podcast number 539 where I showed a formula that basically took someone's birthday and converted it to this year, the situation there was someone was trying to send birthday cards out to their customer base they were looking for the customers that had birthdays next five to seven days or something like that.
Van said; you know he'd be a great idea if you took that one step further and actually red flag the customers who were five to eight days away that we'd be able to do it very quickly so I built a formula here in column D that says basically we're going to take the date in C2, equals C2, minus the today function =C2-TODAY(), the today function is going to give us today's date, if the birthdays already passed that's going to be a negative number we'll just keep that in mind, and what I'm going to do is I'm going to conditional format this entire column so I'm going to select the column and then use format, conditional formatting and say that if the cell value is between let's say five and ten days away then I will apply a special format maybe a pattern of red and a font of white, click ok, click ok and now as we scroll through you'll see any birthday cards that we need to send out today are automatically flagged in red.
Now the beautiful thing about the today function is every time that I open this document, today will be recalculated and the customers are red will automatically just scroll down through the data set when we get to the next year it'll automatically work that will see the customers back in January being highlighted.
Great idea from Van, I want to thank him for sending in that suggestion, I want to thank you for stopping by; I will see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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