Dee-Dee has a column in Excel with Date-of-Birth. She wants to sort this column to assist her with sending out birthday cards. Podcast 1901 shows two formulas, one short that requires some scrolling and one insanely long that will always sort the next birthdays to the top.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1901.
Sort Date of Birth into Birthday Card Sequence.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question, asked by Dee-Dee at one of my seminars.
I was in Baltimore and Dee-Dee said: hey, look, we have this column of birth dates here and I need to send birthday cards to these people, how can I sort this, so that way the birthdays that are coming up next, are first in the list?
And see, of course when we sort it, the oldest person, so in 1922, date of birth is showing up first.
It has nothing to do with month and I have two solutions to this.
One, really easy and then, one far more complicated.
So let's start with the easy one, where we use =TEXT(C2,”MM-DD”) of that date in C2, comma, and then in quotes we're going say we want it in MM-DD format.
The MM is really important, because it will give us 01 for January, instead of just 1 and that will be a very nice sortable sequence.
So double click to copy that down and then sort A-to-Z, and you'll see that January 2nd is coming up first.
So now, it's currently August 4th, you just have to scroll through, you know, and these are the people whose birthdays are coming up next.
Now, if you really wanted to see the birthdays that were coming up next at the top, then we have to do this much, much harder formula, where we ask for the date and the year of today after today, put open and closing parentheses, closing parenthesis for the year.
And then the month of C2, and the day of C2, closing parenthesis for the whole thing.
And that's going to create the birthday fast-forwarded into this year (=DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))).
So all of these birth dates in 2014, which is when I'm recording this.
However, there's a problem, January 2nd, the next birthday, is actually January 2nd of 2015, not 2014, because we're past that date on the calendar.
So watch this, I'm going to… I pressed F2 to put it in edit mode, I select this entire formula except for the equal sign and Ctrl+C to copy.
And then, right after the year, so before the comma of year, I'm going to say +IF and then I'll paste, Ctrl+V, is less than today, open parenthesis, close parenthesis, then we want to add one year, otherwise add 0 years, closing parentheses (<TODAY(),1,0)) and copy that down.
And what this gets us is: every day it's going to show us the next time this person is having a birthday.
And now when we sort by this again, it's August 4th, when I'm recording this, we’ll see the people, whose birthday is next.
8/5/2014, all the way down here, through the bottom, the people whose birthday just passed, July 30th 2015.
Now, this is a great way, if your job involves sending out birthday cards those important clients, or you just have a birthday card list.
This formula is a great way to take their date of birth here and convert it into either something you can sort and scroll through to find the next people, or this one here will always sort the upcoming birthdays into the list.
Much harder to create this formula for the first time, but then life will be easier as you go on, you just simply have to come in, open the file, choose one cell in the Sort By column, click A-to-Z and you will see the people, whose birthday cards are coming up next.
Alright, hey, I want to thank Dee-Dee for sending that question in and I want to thank you for stopping by, we’ll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1901.
Sort Date of Birth into Birthday Card Sequence.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question, asked by Dee-Dee at one of my seminars.
I was in Baltimore and Dee-Dee said: hey, look, we have this column of birth dates here and I need to send birthday cards to these people, how can I sort this, so that way the birthdays that are coming up next, are first in the list?
And see, of course when we sort it, the oldest person, so in 1922, date of birth is showing up first.
It has nothing to do with month and I have two solutions to this.
One, really easy and then, one far more complicated.
So let's start with the easy one, where we use =TEXT(C2,”MM-DD”) of that date in C2, comma, and then in quotes we're going say we want it in MM-DD format.
The MM is really important, because it will give us 01 for January, instead of just 1 and that will be a very nice sortable sequence.
So double click to copy that down and then sort A-to-Z, and you'll see that January 2nd is coming up first.
So now, it's currently August 4th, you just have to scroll through, you know, and these are the people whose birthdays are coming up next.
Now, if you really wanted to see the birthdays that were coming up next at the top, then we have to do this much, much harder formula, where we ask for the date and the year of today after today, put open and closing parentheses, closing parenthesis for the year.
And then the month of C2, and the day of C2, closing parenthesis for the whole thing.
And that's going to create the birthday fast-forwarded into this year (=DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))).
So all of these birth dates in 2014, which is when I'm recording this.
However, there's a problem, January 2nd, the next birthday, is actually January 2nd of 2015, not 2014, because we're past that date on the calendar.
So watch this, I'm going to… I pressed F2 to put it in edit mode, I select this entire formula except for the equal sign and Ctrl+C to copy.
And then, right after the year, so before the comma of year, I'm going to say +IF and then I'll paste, Ctrl+V, is less than today, open parenthesis, close parenthesis, then we want to add one year, otherwise add 0 years, closing parentheses (<TODAY(),1,0)) and copy that down.
And what this gets us is: every day it's going to show us the next time this person is having a birthday.
And now when we sort by this again, it's August 4th, when I'm recording this, we’ll see the people, whose birthday is next.
8/5/2014, all the way down here, through the bottom, the people whose birthday just passed, July 30th 2015.
Now, this is a great way, if your job involves sending out birthday cards those important clients, or you just have a birthday card list.
This formula is a great way to take their date of birth here and convert it into either something you can sort and scroll through to find the next people, or this one here will always sort the upcoming birthdays into the list.
Much harder to create this formula for the first time, but then life will be easier as you go on, you just simply have to come in, open the file, choose one cell in the Sort By column, click A-to-Z and you will see the people, whose birthday cards are coming up next.
Alright, hey, I want to thank Dee-Dee for sending that question in and I want to thank you for stopping by, we’ll see you next time for another netcast – MrExcel.
