Learn Excel – 15 Years Ago Today – Convert Date to Month – Podcast #1829


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.

Leave a Reply

Your email address will not be published. Required fields are marked *