From a YouTube Viewer, Bill looks how to have text and actual dates reside in the same Cell. Follow along with Episode #1517 to learn how to add Custom text to your Date Field while retaining the actual Date.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1517.
Words with Dates.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in from YouTube, someone has a spreadsheet where they have some payroll data and so they have text up here or pay as of a particular date and then further on down two weeks later, they have the next date.
They said you know, is there any way to get this cell, cell A8 to calculate?
Based on cell A1.
And I'm sure they were hoping that I was going to use some combination of the date function with MID and RIGHT and all kinds of things like that, but I'm going to go a completely different way.
I'm gonna go to the second worksheet here, and I'm going to just put in a date up here 2/17/2012.
All right! So, there's our initial date and then I'm going to go into format cells.
Now, for format cells, we can go to the home tab and click this little dialog launcher here or we can just use [ ctrl 1 ], once we get to format cells, that's the [ ctrl 1 ].
I see that's currently set up as a date.
I'm going to click custom, and that'll show me the custom number format for that date.
But then I'm going to click before the format and in quotes.
I'm going to say, pay roll, as of make sure to put a [ space ] and then a closing quote and click [ ok ], all right!
So, now we see the words payroll as of but we actually have a true date there.
So, then the formula here well, this is simple equal 14 plus formula above and we'll copy down here and each additional payroll period.
That way it is actually, storing a real date there but we're able to use custom number formatting to change the way that Excel is showing us that date.
Hey! Want to thank you for stopping by, we'll see you next time another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1517.
Words with Dates.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in from YouTube, someone has a spreadsheet where they have some payroll data and so they have text up here or pay as of a particular date and then further on down two weeks later, they have the next date.
They said you know, is there any way to get this cell, cell A8 to calculate?
Based on cell A1.
And I'm sure they were hoping that I was going to use some combination of the date function with MID and RIGHT and all kinds of things like that, but I'm going to go a completely different way.
I'm gonna go to the second worksheet here, and I'm going to just put in a date up here 2/17/2012.
All right! So, there's our initial date and then I'm going to go into format cells.
Now, for format cells, we can go to the home tab and click this little dialog launcher here or we can just use [ ctrl 1 ], once we get to format cells, that's the [ ctrl 1 ].
I see that's currently set up as a date.
I'm going to click custom, and that'll show me the custom number format for that date.
But then I'm going to click before the format and in quotes.
I'm going to say, pay roll, as of make sure to put a [ space ] and then a closing quote and click [ ok ], all right!
So, now we see the words payroll as of but we actually have a true date there.
So, then the formula here well, this is simple equal 14 plus formula above and we'll copy down here and each additional payroll period.
That way it is actually, storing a real date there but we're able to use custom number formatting to change the way that Excel is showing us that date.
Hey! Want to thank you for stopping by, we'll see you next time another netcast from MrExcel.
