Today, Nick asks how to get the word 'August' in cell B1 from a date in cell A1. In Episode #1414, Bill shows us how to convert a date to a month name or to a weekday name using the TEXT function.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1414.
August from 8/25/2011.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Nick.
Nick has a date up there in cell A1 and in cell B1.
He wants a formula.
It's gonna give him the month named august and Nick was trying.
He was trying month and then equal vlookup, of that and just this huge thing, but there's a much much easier way to go.
You know, we have that date up there, and if we go into format cells [ ctrl 1 ].
They give us a few different date formats, but there's a whole bunch of custom date formats and if we put in 4 m's, you see that it actually spells out the month name.
So, the trick there is, to say we want equal text.
Text is going to take a value and show it in a certain custom number format.
Some of the text of A1 in quotes, 4 m's (mmmm) and it will convert that August to August.
We'll try another date 1/23/2012, converts it to January and set on, all right!
Let's take a look at all the different date formats that are available.
Single d, will show us just the one or two digit date.
2 d's, will put a leading 0 in front of that date.
3 d's, gives us the weekday abbreviation.
4 d's, spells out the weekday.
Month, with a leading 0.
3 m's, gives us the month abbreviation.
4 m's, gives us the whole thing.
Strangely enough, 'y' actually works with one, two, three or four.
Although, you either get a two digit year or a four-digit year.
1 and 2 y's three and four y's, return the same value.
So, you know if you wanted to see that what day of the week it was equal text that date comma in quotes 4 d's.
Will give us Thursday, here we go.
A cool way to convert a date to either month name or the weekday.
I want to thank you for stopping by, see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1414.
August from 8/25/2011.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in by Nick.
Nick has a date up there in cell A1 and in cell B1.
He wants a formula.
It's gonna give him the month named august and Nick was trying.
He was trying month and then equal vlookup, of that and just this huge thing, but there's a much much easier way to go.
You know, we have that date up there, and if we go into format cells [ ctrl 1 ].
They give us a few different date formats, but there's a whole bunch of custom date formats and if we put in 4 m's, you see that it actually spells out the month name.
So, the trick there is, to say we want equal text.
Text is going to take a value and show it in a certain custom number format.
Some of the text of A1 in quotes, 4 m's (mmmm) and it will convert that August to August.
We'll try another date 1/23/2012, converts it to January and set on, all right!
Let's take a look at all the different date formats that are available.
Single d, will show us just the one or two digit date.
2 d's, will put a leading 0 in front of that date.
3 d's, gives us the weekday abbreviation.
4 d's, spells out the weekday.
Month, with a leading 0.
3 m's, gives us the month abbreviation.
4 m's, gives us the whole thing.
Strangely enough, 'y' actually works with one, two, three or four.
Although, you either get a two digit year or a four-digit year.
1 and 2 y's three and four y's, return the same value.
So, you know if you wanted to see that what day of the week it was equal text that date comma in quotes 4 d's.
Will give us Thursday, here we go.
A cool way to convert a date to either month name or the weekday.
I want to thank you for stopping by, see you next time for another netcast from MrExcel.
