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!
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!
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.
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.
