Rick from Alabama sends in today's question: "How can I join two date cells into a single cell?" While Excel initially won't cooperate, Episode #1262 shows the function that you can use to answer this question.
Learn Excel 97-2007 from MrExcel!
Learn Excel 97-2007 from MrExcel!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1262 - Joining Dates.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question sent in by Rick. Rick is from Alabama.
Rick wanted to join two dates and he was interested in and Excel would not cooperate.
So, he has a start date and an end date.
He wanted to format that nicely.
So, he did =A2& and then in quotes a dash (''-'') ampersand (&) at the other date, and it converts it back to the serial number.
That's how Excel is actually storing the dates.
You know, September 22nd 2010 is forty thousand four hundred forty three days.
Since January 1st 1900, on a windows PC.
Alright so, to solve this we're not going to take A2.
We'll take the TEXT of A2, the text of A2 and then in quotes (" "), we're going to put a custom number format.
For example m/d/yyyy would be one way to do it.
Now close the quotes("), close the parentheses ( ) and then out here instead of B2 - the TEXT of B2, and again m/d/yyyy.
And, we will now have a properly formatted cell with the real dates.
Now, you actually have a lot of control here over exactly how those dates are going to appear.
So, if you wanted to you can spell out the month with four m's (mmmm) d, yyyy or let's see what else you might want to just spell use the month abbreviation so mmm and then d, and just two-digit year(yy) instead of a four-digit year(yyyy).
So, you see! you have some control over how that is going to appear.
Anytime you're joining text with either a date or with currency, you'll probably want to use that text function to control exactly how that item is going to appear.
Okay, hey! I want to thank Rick for sending in the question.
Thank you for stopping by. We will see you next time with another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1262 - Joining Dates.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question sent in by Rick. Rick is from Alabama.
Rick wanted to join two dates and he was interested in and Excel would not cooperate.
So, he has a start date and an end date.
He wanted to format that nicely.
So, he did =A2& and then in quotes a dash (''-'') ampersand (&) at the other date, and it converts it back to the serial number.
That's how Excel is actually storing the dates.
You know, September 22nd 2010 is forty thousand four hundred forty three days.
Since January 1st 1900, on a windows PC.
Alright so, to solve this we're not going to take A2.
We'll take the TEXT of A2, the text of A2 and then in quotes (" "), we're going to put a custom number format.
For example m/d/yyyy would be one way to do it.
Now close the quotes("), close the parentheses ( ) and then out here instead of B2 - the TEXT of B2, and again m/d/yyyy.
And, we will now have a properly formatted cell with the real dates.
Now, you actually have a lot of control here over exactly how those dates are going to appear.
So, if you wanted to you can spell out the month with four m's (mmmm) d, yyyy or let's see what else you might want to just spell use the month abbreviation so mmm and then d, and just two-digit year(yy) instead of a four-digit year(yyyy).
So, you see! you have some control over how that is going to appear.
Anytime you're joining text with either a date or with currency, you'll probably want to use that text function to control exactly how that item is going to appear.
Okay, hey! I want to thank Rick for sending in the question.
Thank you for stopping by. We will see you next time with another netcast from MrExcel.
