It was the Saturday before Thanksgiving in 1998 when I opened Notepad and launched the first tip of the week at MrExcel.com. Using the Wayback Machine, see how the tip for converting daily dates to months holds up 15 years later.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1829: 15 years ago Today - Convert Dates to Months.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, this is the Saturday before.
Thanksgiving here in the United States and it was 15 years ago today the Saturday before Thanksgiving when I open notepad and launched a tiny little website called MrExcel.com.
Thanks for the Wayback Machine this is what it looked like this is actually from about May 19,1999.
They finally picked it up we can go back and see the very first tip of the week for November 21st 1998.
Kevin, my friend Kevin from a Merivale says I have a table of events in Excel.
Each row has a date indicating the date the event occurred.
I want to use a pivot table to summarize the data.
I want to see it at the month level instead of the daily level.
Try simply formatting the date column to display a month, but the pivot table still breaks the data out by day.
So, this would have been back in Excel 97 or Excel 95 I'm not even sure what I was using at the time and I suggested this formula here isn't this crazy to go back and look at the old old MrExcel.
So, I thought I'd revisit, this is a very first question that we ever posted and take a look at some new ways to do that.
If you happen to be watching this during the last week of November go out to MrExcel.com the Excel store on the left hand side there's a special category they're called 15th anniversary.
I have 15 different Excel e-books from extreme beginner to Excel guru that are all priced at a special $7.50 price no shipping of course on the e-book so those work worldwide.
Just as a thank you to all the people have supported MrExcel.com over the years.
Alright, so here we are in Excel 2013 and Kevin wanting to convert those daily Dates to a Month and the solution that I had back then was = (date) -day of that date +1 to get me back to the first of the month and that would allow the pivot table to work.
Of course, there are better ways to do this.
Today back then the analysis tool pack was not everywhere it wasn't installed in a lot of companies so today we have the the old analysis tool pack functions and we can use EOMONTH of that day we want the -1 month +1 which will get us back to and the EOMONTH gives us the the end of the previous month plus one it gets us back up to the next day or hey I probably didn't even know it at the time the text function would be a way to do this so =text of that date and we could say that it is in yyy-mmm format although of course when we get to the pivot table that is going to not sort correctly.
So, that one goes away are on the fast way to do this today, so we have daily dates here we want to create a pivot table by month just go ahead and create the pivot table insert pivot table, click ok, we're going to put daily dates down the left-hand side and then whatever you want in the heart of the pivot table so maybe revenue will move that off to the side.
Choose the very first date field and up here choose a group and then Years and Months, click OK.
We now have a report it took those daily dates rolled up two Months and Years isn't amazing how Excel has improved so much since 15 years ago.
So, much faster way to go.
And sincerely, I want to thank you for supporting MrExcel.com over the last 15 years.
I truly enjoy learning new tips and passing along and my website these video podcasts and my live seminars.
Without you, wouldn't be possible thanks for help me get from here to here.
Hey, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1829: 15 years ago Today - Convert Dates to Months.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, this is the Saturday before.
Thanksgiving here in the United States and it was 15 years ago today the Saturday before Thanksgiving when I open notepad and launched a tiny little website called MrExcel.com.
Thanks for the Wayback Machine this is what it looked like this is actually from about May 19,1999.
They finally picked it up we can go back and see the very first tip of the week for November 21st 1998.
Kevin, my friend Kevin from a Merivale says I have a table of events in Excel.
Each row has a date indicating the date the event occurred.
I want to use a pivot table to summarize the data.
I want to see it at the month level instead of the daily level.
Try simply formatting the date column to display a month, but the pivot table still breaks the data out by day.
So, this would have been back in Excel 97 or Excel 95 I'm not even sure what I was using at the time and I suggested this formula here isn't this crazy to go back and look at the old old MrExcel.
So, I thought I'd revisit, this is a very first question that we ever posted and take a look at some new ways to do that.
If you happen to be watching this during the last week of November go out to MrExcel.com the Excel store on the left hand side there's a special category they're called 15th anniversary.
I have 15 different Excel e-books from extreme beginner to Excel guru that are all priced at a special $7.50 price no shipping of course on the e-book so those work worldwide.
Just as a thank you to all the people have supported MrExcel.com over the years.
Alright, so here we are in Excel 2013 and Kevin wanting to convert those daily Dates to a Month and the solution that I had back then was = (date) -day of that date +1 to get me back to the first of the month and that would allow the pivot table to work.
Of course, there are better ways to do this.
Today back then the analysis tool pack was not everywhere it wasn't installed in a lot of companies so today we have the the old analysis tool pack functions and we can use EOMONTH of that day we want the -1 month +1 which will get us back to and the EOMONTH gives us the the end of the previous month plus one it gets us back up to the next day or hey I probably didn't even know it at the time the text function would be a way to do this so =text of that date and we could say that it is in yyy-mmm format although of course when we get to the pivot table that is going to not sort correctly.
So, that one goes away are on the fast way to do this today, so we have daily dates here we want to create a pivot table by month just go ahead and create the pivot table insert pivot table, click ok, we're going to put daily dates down the left-hand side and then whatever you want in the heart of the pivot table so maybe revenue will move that off to the side.
Choose the very first date field and up here choose a group and then Years and Months, click OK.
We now have a report it took those daily dates rolled up two Months and Years isn't amazing how Excel has improved so much since 15 years ago.
So, much faster way to go.
And sincerely, I want to thank you for supporting MrExcel.com over the last 15 years.
I truly enjoy learning new tips and passing along and my website these video podcasts and my live seminars.
Without you, wouldn't be possible thanks for help me get from here to here.
Hey, we'll see you next time for another netcast from MrExcel.
