I bet you thought that I screwed up and put the date of the podcast as the title! But no... that really is the title. Tony asks how to change the long date format to show Thu Mar 5, 2009. Episode 964 shows you how.
This video is the 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 video is the 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 going to analyze as well let's fire up a pivot table.
Let's see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Want to talk about number formats today.
Someone wrote in and said. You know, they have 2 date formats.
The short date and the long date.
Why don't they have a date format that you know, lets you have some more control like, you know, the day abbreviation or the month abbreviation or something like that. .
And so right here, I have this whole range of dates.
Today's date and the various different number formats.
I'm going to apply all of these number formats to the cell Immediately to the left, or a little, tiny little macro to do this,.
just the three line macro for each cell in selection.
The number format for that cell comes from one column to the right.
Whatever value happens to be there.
So that runs that macro and you don't have to sit here and watch me apply all the number formats.
But here's what happens when you have a single d in the custom number format.
You're getting just the date like 5 and dd gives you 05.
Now ofcourse you know after the 10th, It doesn't matter.
It's the same.
ddd gives you the abbreviation. dddd spells out the day of the week, and then a single m gives you the month as a number without the leading 0.
mm gives you the leading 0. mmm the abbreviation, mmmm spells out the month, and then mmmmm, this is great when you're creating charts like in the wall street journal across the bottom JFM AM J ASOND that mmmmm abbreviation will do that. So now you can combine it as you want.
So here's a custom number format of ddd,mmm d, yyyy Well, what that's going to do for me is give me the day abbreviation.
So I'm going to get Thu for Thursday.
The month abbreviation Mar for March, and then because I don't have dd, I'm not going to get 05 and then a comma 2009. So you can see that custom number format there ends up with exactly what we want.
Now once you've applied this custom number format and generally we're going to format cells.
Ctrl 1. Once you've applied that custom number format, they're going to be there in the custom list.
It's just the first time when you actually have to go in and type all this in.
You know it is a little bit tedious, but then after that you're good to go.
Hey, I want to thank you for stopping by. we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically we start out with massive amounts of data.
So, how we're going to analyze as well let's fire up a pivot table.
Let's see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Want to talk about number formats today.
Someone wrote in and said. You know, they have 2 date formats.
The short date and the long date.
Why don't they have a date format that you know, lets you have some more control like, you know, the day abbreviation or the month abbreviation or something like that. .
And so right here, I have this whole range of dates.
Today's date and the various different number formats.
I'm going to apply all of these number formats to the cell Immediately to the left, or a little, tiny little macro to do this,.
just the three line macro for each cell in selection.
The number format for that cell comes from one column to the right.
Whatever value happens to be there.
So that runs that macro and you don't have to sit here and watch me apply all the number formats.
But here's what happens when you have a single d in the custom number format.
You're getting just the date like 5 and dd gives you 05.
Now ofcourse you know after the 10th, It doesn't matter.
It's the same.
ddd gives you the abbreviation. dddd spells out the day of the week, and then a single m gives you the month as a number without the leading 0.
mm gives you the leading 0. mmm the abbreviation, mmmm spells out the month, and then mmmmm, this is great when you're creating charts like in the wall street journal across the bottom JFM AM J ASOND that mmmmm abbreviation will do that. So now you can combine it as you want.
So here's a custom number format of ddd,mmm d, yyyy Well, what that's going to do for me is give me the day abbreviation.
So I'm going to get Thu for Thursday.
The month abbreviation Mar for March, and then because I don't have dd, I'm not going to get 05 and then a comma 2009. So you can see that custom number format there ends up with exactly what we want.
Now once you've applied this custom number format and generally we're going to format cells.
Ctrl 1. Once you've applied that custom number format, they're going to be there in the custom list.
It's just the first time when you actually have to go in and type all this in.
You know it is a little bit tedious, but then after that you're good to go.
Hey, I want to thank you for stopping by. we'll see you next time for another netcast from MrExcel.
