May 06 2022 07:18 AM
Hi, i have a list of dates where an activity (installation) occurred...and I would like to build a graph showing the volume of installations per day - across a period of several years.
e.g. over several years installations were done each day...sometimes more than 1 in a day...in this case there would be two separate occurencees of that date because there was 2 installations. See below there are 2 dates for 25th.
as such...
Installed |
08/08/2017 |
04/09/2017 |
06/09/2017 |
25/09/2017 |
25/09/2017 |
I would like to see this in a graph so i can visually represent the busiest periods.
When I take this list and create a pivot table, and then create a line graph....the graph is too wide (as it covers several years)...so i opted for a monthly rate....converting the dates into Year_Month format, as follows:
Install Month_Year |
2017August |
2017September |
2017September |
2017September |
2017September |
However now when i create a pivot table - to show how many occurred in September...it sorts the dates alphabetically....e.g. 2017August would be followed by 2017December. Then my line graph is not running in date order.
So my question is - how do I resort the pivot table so it is back in date order....ro is there an entirely better way of doing this in the first place!
Any help appreciated.
Den.
May 06 2022 07:27 AM
This table that you displayed is text, and so Excel is doing its job correctly when it sorts alphabetically.
Install Month_Year |
2017August |
2017September |
2017September |
2017September |
2017September |
You need to make sure your dates--the dates you use as the horizontal axis in your Pivot Table-- are actual Excel dates. It should seamlessly give you the ability to collapse those into months, and do them sequentially in calendar order rather than alphabetical order.
May 06 2022 07:27 AM
I guess "2017August" is the text, and texts are sorted alphabetically. You may format dates as "yyyy mmmm" and use them as dates.
May 06 2022 07:30 AM
May 06 2022 07:56 AM
May 06 2022 07:56 AM
Excel is pretty smart. I have a spreadsheet in which I have a huge data table consisting of all my financial transactions (checks, credit card charges, deposits, whatever). Each row has a date, standard Excel "Short date" format....mo/da/yr .... and my pivot table pretty much assumes I want it to take those very detailed rows of information and display the income and expense categories by month. (The months are the horizontal axis; categories of income/expense are the vertical axis; I have year set as a Filter, since I usually display just a single year's worth of data.....
So my main point, you should be able to just use the normal natural full date, and let Excel do its thing. If that doesn't work, if you're seeing too much daily detail, then add a column for month and year (still using Excel's formatting of real underlying dates)
May 06 2022 07:58 AM
May 06 2022 08:00 AM
Solution
See the attached file. The data table has full dates displayed. The pivot table summarizes by month.
May 06 2022 08:04 AM
My point so far is that you won't need to format by month and year. Pivot Table is smart enough to provide that option automatically, working from a full date field in the underlying data.
Nevertheless, if you want to display a real Excel date solely as Month-Year, it's available under "More Number Formats" -- the first dialog box looks like this
In the next dialog box, select Date and then scroll down along the many options until you find the one that is Month and Year only
May 06 2022 08:18 AM
@mathetes I see - I was making it more complicated/harder! thanks again....Ive now got what I need - much appreciated!
May 06 2022 08:40 AM
I was making it more complicated/harder!
Yes indeed. That's easy to do. We all do it from time to time, over-think things. As a general rule of thumb, I've come to the realization (and recommend it as a good habit of mind to you as well): "If I think Excel really ought to do ___________________" then the chances are that it DOES. The clever people who've programmed Excel have thought the same and have made it possible. The trick--and this can take some time--is finding where they buried it. Or, in this case, where it's lying right out there in the open and just works like you want it to, without drawing attention to itself.
thanks again....Ive now got what I need - much appreciated!
You're very welcome.
May 06 2022 08:00 AM
Solution
See the attached file. The data table has full dates displayed. The pivot table summarizes by month.