Chris has 400K records from the Board of Elections. The date of birth field is a string of 8 digits in YYYYMMDD format. Today's episode shows how to convert that number to a useful date and then how to apply a filter to copy everyone born in a certain decade.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel episode: 1809 Filter Board of election Data in Year-month-Day format.
Hey, everyone welcome back to the MrExcel netcast.
I just got back from Fort Myers, Florida.
Did a great power Excel seminar down there for 73 folks and one of the people in the audience was Chris.
Chris gets a data set from the Board of Elections with 400,000 voter names Of course he has more information here.
You know, you can just imagine that there's data filled in but the important part the question that Chris had is, "They put the date of birth information in just a single string.
8-digits with a 4-digit year, 2-digit month, 2-digit day." And Chris needs to go through here and get all of the people who are a certain age.
Maybe everyone who was born in the 1950s because they're going to do a mailing to those particular people.
All right. So, good news!
I'm very surprised that Excel is able to handle this way that they do.
I'm going to start here in cell A2.
[ CTRL+SHIFT+down-arrow ] to go down to the end of the data.
To turn these into real dates, go back to the Data Tab > Text to Columns.
Doesn't matter what we choose on the first two steps.
On the third step though we want to choose date and go to YMD format.
Click Finish.
And they've converted that data to real date's.
Now, that is real dates.
The Excel tool is like FILTER and all the DATE functions will work.
I want to turn on the filter here on the data Tab and they now have in Excel 2010.
Date filters, where we can ask for Between.
Let's say they need everything in the 1950's.
So, is equal to equal or after 1/1/1950.
We're just to put that date in there.
Down through 12/31/1959.
Click Ok.
All right, you can see if they've hidden all the other rows.
Chris wants to take that to a new worksheet.
So, [ CTRL+A ] to select all, [ CTRL+C ] to Copy, [ CTRL+N ] for new.
[ CTRL+V ] to paste and then you need to make column A a little bit wider there.
And what we have I'll do CTRL period Twice.
So, 7000 out of my 50,000 original records.
7000 are in that range of dates.
All right! So, there you go.
A couple of different tricks if you have dates in an 8-digit format for a year 2-digit month, 2-digit day, you can convert those and then the filter offering this nice set of date filters for dates between.
I want to thank Chris for coming to my seminar and asking that great question.
I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Learn Excel from MrExcel episode: 1809 Filter Board of election Data in Year-month-Day format.
Hey, everyone welcome back to the MrExcel netcast.
I just got back from Fort Myers, Florida.
Did a great power Excel seminar down there for 73 folks and one of the people in the audience was Chris.
Chris gets a data set from the Board of Elections with 400,000 voter names Of course he has more information here.
You know, you can just imagine that there's data filled in but the important part the question that Chris had is, "They put the date of birth information in just a single string.
8-digits with a 4-digit year, 2-digit month, 2-digit day." And Chris needs to go through here and get all of the people who are a certain age.
Maybe everyone who was born in the 1950s because they're going to do a mailing to those particular people.
All right. So, good news!
I'm very surprised that Excel is able to handle this way that they do.
I'm going to start here in cell A2.
[ CTRL+SHIFT+down-arrow ] to go down to the end of the data.
To turn these into real dates, go back to the Data Tab > Text to Columns.
Doesn't matter what we choose on the first two steps.
On the third step though we want to choose date and go to YMD format.
Click Finish.
And they've converted that data to real date's.
Now, that is real dates.
The Excel tool is like FILTER and all the DATE functions will work.
I want to turn on the filter here on the data Tab and they now have in Excel 2010.
Date filters, where we can ask for Between.
Let's say they need everything in the 1950's.
So, is equal to equal or after 1/1/1950.
We're just to put that date in there.
Down through 12/31/1959.
Click Ok.
All right, you can see if they've hidden all the other rows.
Chris wants to take that to a new worksheet.
So, [ CTRL+A ] to select all, [ CTRL+C ] to Copy, [ CTRL+N ] for new.
[ CTRL+V ] to paste and then you need to make column A a little bit wider there.
And what we have I'll do CTRL period Twice.
So, 7000 out of my 50,000 original records.
7000 are in that range of dates.
All right! So, there you go.
A couple of different tricks if you have dates in an 8-digit format for a year 2-digit month, 2-digit day, you can convert those and then the filter offering this nice set of date filters for dates between.
I want to thank Chris for coming to my seminar and asking that great question.
I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
