Forum Discussion
sorting dates
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.
See the attached file. The data table has full dates displayed. The pivot table summarizes by month.
10 Replies
I guess "2017August" is the text, and texts are sorted alphabetically. You may format dates as "yyyy mmmm" and use them as dates.
- 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 monthSee the attached file. The data table has full dates displayed. The pivot table summarizes by month.
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.
- 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.
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)
