George would like to mail birthday cards to all of his customers. He has date of birth in his database, but unfortunately, comparing =TODAY() to a birthday in 1942 doesnt provide a lot of information. In Episode 539, we take a look at the DATE function used to solve this problem.
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:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question sent in by George.
If you have a question for the netcast, please feel free to drop me an email, leave me a voicemail and we'll get to you on a future podcast.
So, George works in a customer service department where they have a number of customers.
He'd like to be able to send out birthday cards to all of his regular customers.
He'd like to be able to do this about a week ahead of time.
Now, in George's database here, he has a list of customers and their birthday.
But when he tries to use any function like perhaps the today function.
=TODAY() minus the birth date to see if anything's in the next 7 days or something like that.
We're getting strange answers.
First of all, let's format this just as a number, so we can see how many days away the birthday is. Click OK, and we get a huge number like 23,000.
Well, that's saying that this customer was born 23,000 days ago and George really, just wants to know if their birthday's coming up in the next 7 days.
So, what we have to do is, we have to come up with a calculation that wil figure out when their birthday falls this year.
So, I'm going to insert a new column and I'm going to call it Birthday 2007.
And we're going to use the DATE function.
The DATE function is this fabulous function in excel, where we give it a year a month and a day.
So, I'll ask for the date.
2007 and then which month do I want? I want the month from the customers birthday.
So, I'll ask for the the month function of B2, and then which day do I want? Again, I what the day of the customers birthday.
So, I'll ask for the day of B2.
So, basically what I'm asking for here is the 2007th year, the 6th month, the 2nd day.
And that will give me a date that falls in this year.
So, this year, the customers birthday is June 2nd 2007.
Now, when I change my formula to compare today, to the customers birthday, =C2-TODAY(), I'm going to get the actual number of days from now until the birthday.
So, for example, in row 3 Bobby's birthday is 154 days from now, not time to send the card yet.
So, I'll call this Days away.
And perhaps, we'll do a sort ascending, and basically, then I'll look for any customers, whose birthdays are coming up in the next 7 days.
So, Anita and Sarah, send out their birthday cards now.
Now, when I come back in next week and open this, ofcourse, the TODAY function's going to recalculate.
So, I'll just have to resort the data and very quickly again, I'll be able to find those customers, whose birthdays are coming up again.
The key to this was to use the DATE function.
Again, that was date, and then we specify a year, a month and a day.
In this case, I'm reusing the month and day from column B, the actual birthday, but in the year, putting 2007.
So, thanks to George for sending in that question, and thanks to you for stopping by.
Will see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question sent in by George.
If you have a question for the netcast, please feel free to drop me an email, leave me a voicemail and we'll get to you on a future podcast.
So, George works in a customer service department where they have a number of customers.
He'd like to be able to send out birthday cards to all of his regular customers.
He'd like to be able to do this about a week ahead of time.
Now, in George's database here, he has a list of customers and their birthday.
But when he tries to use any function like perhaps the today function.
=TODAY() minus the birth date to see if anything's in the next 7 days or something like that.
We're getting strange answers.
First of all, let's format this just as a number, so we can see how many days away the birthday is. Click OK, and we get a huge number like 23,000.
Well, that's saying that this customer was born 23,000 days ago and George really, just wants to know if their birthday's coming up in the next 7 days.
So, what we have to do is, we have to come up with a calculation that wil figure out when their birthday falls this year.
So, I'm going to insert a new column and I'm going to call it Birthday 2007.
And we're going to use the DATE function.
The DATE function is this fabulous function in excel, where we give it a year a month and a day.
So, I'll ask for the date.
2007 and then which month do I want? I want the month from the customers birthday.
So, I'll ask for the the month function of B2, and then which day do I want? Again, I what the day of the customers birthday.
So, I'll ask for the day of B2.
So, basically what I'm asking for here is the 2007th year, the 6th month, the 2nd day.
And that will give me a date that falls in this year.
So, this year, the customers birthday is June 2nd 2007.
Now, when I change my formula to compare today, to the customers birthday, =C2-TODAY(), I'm going to get the actual number of days from now until the birthday.
So, for example, in row 3 Bobby's birthday is 154 days from now, not time to send the card yet.
So, I'll call this Days away.
And perhaps, we'll do a sort ascending, and basically, then I'll look for any customers, whose birthdays are coming up in the next 7 days.
So, Anita and Sarah, send out their birthday cards now.
Now, when I come back in next week and open this, ofcourse, the TODAY function's going to recalculate.
So, I'll just have to resort the data and very quickly again, I'll be able to find those customers, whose birthdays are coming up again.
The key to this was to use the DATE function.
Again, that was date, and then we specify a year, a month and a day.
In this case, I'm reusing the month and day from column B, the actual birthday, but in the year, putting 2007.
So, thanks to George for sending in that question, and thanks to you for stopping by.
Will see you next time for another netcast from MrExcel.
