If you try to use the concatenation character to join text with a date, you will not get the results that you expected. Episode 414 shows you how to modify the formula to properly format the date.
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:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Last Friday, episode 412, I showed you how to use the concatenation character.
(A2&” “&B2) to join text from column A and column B. I want to point out, there's a problem here, if you try and join text and dates.
So if I add to this formula and say it, for example, “was born on”&C2 to get that date in situ.
Instead of getting January 18th 1958, I'm getting 21203.
Well, what is 21203?
That's the number of days since January first 1900, that Toya was born.
Well, that's a fascinating fact, if you're Cliff Clavin.
I've never had a manager call me and say: hey, how many days after January 1st 1900, is that receivable, dude?
Never, ever happens… And what we have to do, in order to format, that cell as text is to use the text function.
So ask for the text of C2 and then a comma, and in quotes I have to put the custom number format.
So I'm going to guess: mm/dd/yyyy.
And sure enough, it now converts it to January 18th 1958.
If you're a person that sits around memorizing all the custom number format codes, you can actually put in any code that you want, for example dddd, mmmm, d, yyyy and it will actually spell out the day and the month for us, however you want to do.
If you know the custom number format codes, feel free to drop them in there, otherwise just use something basic like mm/dd/yyyy.
Basically, that's a great way anytime that you have a date or currency and you're trying to join text, you'll want to use the text function to specify the custom number format.
Hey, thanks for stopping by, we'll see you tomorrow for another 2007 Thursday.
Last Friday, episode 412, I showed you how to use the concatenation character.
(A2&” “&B2) to join text from column A and column B. I want to point out, there's a problem here, if you try and join text and dates.
So if I add to this formula and say it, for example, “was born on”&C2 to get that date in situ.
Instead of getting January 18th 1958, I'm getting 21203.
Well, what is 21203?
That's the number of days since January first 1900, that Toya was born.
Well, that's a fascinating fact, if you're Cliff Clavin.
I've never had a manager call me and say: hey, how many days after January 1st 1900, is that receivable, dude?
Never, ever happens… And what we have to do, in order to format, that cell as text is to use the text function.
So ask for the text of C2 and then a comma, and in quotes I have to put the custom number format.
So I'm going to guess: mm/dd/yyyy.
And sure enough, it now converts it to January 18th 1958.
If you're a person that sits around memorizing all the custom number format codes, you can actually put in any code that you want, for example dddd, mmmm, d, yyyy and it will actually spell out the day and the month for us, however you want to do.
If you know the custom number format codes, feel free to drop them in there, otherwise just use something basic like mm/dd/yyyy.
Basically, that's a great way anytime that you have a date or currency and you're trying to join text, you'll want to use the text function to specify the custom number format.
Hey, thanks for stopping by, we'll see you tomorrow for another 2007 Thursday.
