LearnAccessByCrystal sends in a cool tip that will allow you to format dates to show quarters, weeks, and more. Episode 1074 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So how we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Great tip today, sent in by Crystal.
Crystal does the videos at YouTube under the user id LearnAccessByCrystal.
Crystal's a MVP in the access program and sent along this great tip.
And when Crystal started to tell me about this, I'm, "Crystal we already know this." I've covered it before.
I started talking about the text function. So, we have a date over here in column A and we can pass both the date and a numeric formatting code.
And it will format that date as text, using the numeric formatting code.
So it's a great way, if you're joining text and a date to actually have a lot of explicit control.
And I'm sure that I've covered this in the podcast before.
Like three d's gives you the day abbreviation, four d's spells out the whole day, four m's spells out the whole month.
You know, I use these all the time. Even this one is cool, if you're doing those wall street journal charts and you just need JFM, the first letter of the month, you can put five m's to get the A.
You know, it's been around forever but Crystal's tip is not about text.
It's about a function in VBA called Format and format works just like text for all of these codes but it handles more codes as well. So I'm going to switch over to VBA here, Alt+F11. We will insert a new module and I'm going to create a function.
Function, let's call it, VBAText and we have a value and a format and I'm going to return VBAText = Format, instead of text, (MyValue, MyFmt).
Notice out here that you can also add additional arguments that tell you, whether the first day of the week is Sunday or whether, the first week of the year is the first week with 4 days or the week that contains January 1st and other options there and you'll check out excel or VBA help for that.
All right, so now let's come back here and we'll enter our new function =vbatext, this date using that format and you see for these initial ones, it works exactly the same.
But for all the ones down here, which generally would be considered invalid, it's working.
Let's take a look at these.
A single letter Q will give you the quarter of the year, so yy is giving me 09.
The slash Q is giving me the letter Q and then the 3 there is coming from that single letter q or just slash Q to get the Q and the quarter number. So, I can get quarters, that's cool.
Long date, Medium date, Short Date.
Hey, that's going to return the long date in the local setting of the computer using any internationalization settings. This one is cool.
ww gives you the week number and then w gives you the day within the weekend.
So, this is going to give me Week 32, Day 5.
A single y tells me the number of days this year.
So, August 6, this is 218th day and then 6 d's is the same as typing long date.
So, very cool extension to the TEXT function. Now you have to put in a 3 line VBA Macro to use format, but if you ever needed quarters, certainly a great way to go here to get quarters or week numbers or even the week within the year.
I want to send thanks out to LearnAccessByCrystal for that great tip.
I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
Will see you next time from another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So how we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Great tip today, sent in by Crystal.
Crystal does the videos at YouTube under the user id LearnAccessByCrystal.
Crystal's a MVP in the access program and sent along this great tip.
And when Crystal started to tell me about this, I'm, "Crystal we already know this." I've covered it before.
I started talking about the text function. So, we have a date over here in column A and we can pass both the date and a numeric formatting code.
And it will format that date as text, using the numeric formatting code.
So it's a great way, if you're joining text and a date to actually have a lot of explicit control.
And I'm sure that I've covered this in the podcast before.
Like three d's gives you the day abbreviation, four d's spells out the whole day, four m's spells out the whole month.
You know, I use these all the time. Even this one is cool, if you're doing those wall street journal charts and you just need JFM, the first letter of the month, you can put five m's to get the A.
You know, it's been around forever but Crystal's tip is not about text.
It's about a function in VBA called Format and format works just like text for all of these codes but it handles more codes as well. So I'm going to switch over to VBA here, Alt+F11. We will insert a new module and I'm going to create a function.
Function, let's call it, VBAText and we have a value and a format and I'm going to return VBAText = Format, instead of text, (MyValue, MyFmt).
Notice out here that you can also add additional arguments that tell you, whether the first day of the week is Sunday or whether, the first week of the year is the first week with 4 days or the week that contains January 1st and other options there and you'll check out excel or VBA help for that.
All right, so now let's come back here and we'll enter our new function =vbatext, this date using that format and you see for these initial ones, it works exactly the same.
But for all the ones down here, which generally would be considered invalid, it's working.
Let's take a look at these.
A single letter Q will give you the quarter of the year, so yy is giving me 09.
The slash Q is giving me the letter Q and then the 3 there is coming from that single letter q or just slash Q to get the Q and the quarter number. So, I can get quarters, that's cool.
Long date, Medium date, Short Date.
Hey, that's going to return the long date in the local setting of the computer using any internationalization settings. This one is cool.
ww gives you the week number and then w gives you the day within the weekend.
So, this is going to give me Week 32, Day 5.
A single y tells me the number of days this year.
So, August 6, this is 218th day and then 6 d's is the same as typing long date.
So, very cool extension to the TEXT function. Now you have to put in a 3 line VBA Macro to use format, but if you ever needed quarters, certainly a great way to go here to get quarters or week numbers or even the week within the year.
I want to send thanks out to LearnAccessByCrystal for that great tip.
I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
Will see you next time from another netcast from MrExcel.
