SOLVED

sorting dates

Copper Contributor

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.

 

 

 

 

 

10 Replies

@Fitzpad123 

 

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.

 

@Fitzpad123 

I guess "2017August" is the text, and texts are sorted alphabetically. You may format dates as "yyyy mmmm" and use them as dates.

thank you sergei and mathetes. Makes sense....I didnt realise pivot sorted alphabetically. So I need to take original date (01/02/22) and convert it into month format, but still in date? could you advise what formular i use to do that please? you are correct, I used =text2 before because I thought that would be best way to show the months...but Im happy also if it shows the month in number format.
I dont seem to be able to find this can you advise please?

If I highlight a DATE and choose format cells, I dont see any options that only show month and year...they all include the day of month



@Fitzpad123 

 

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)

thank you - i think i follow...but i dont know how to convert my full date into a month_year version
best response confirmed by Hans Vogelaar (MVP)
Solution

@Fitzpad123 

 

See the attached file. The data table has full dates displayed. The pivot table summarizes by month.

@Fitzpad123 

 

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

mathetes_0-1651849372228.png

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

mathetes_1-1651849481173.png

 

@mathetes I see - I was making it more complicated/harder! thanks again....Ive now got what I need - much appreciated!

 

Fitzpad123_0-1651850260655.png

 

@Fitzpad123 

 

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.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Fitzpad123 

 

See the attached file. The data table has full dates displayed. The pivot table summarizes by month.

View solution in original post