Forum Discussion
sorting dates
See 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)
- thank you - i think i follow...but i dont know how to convert my full date into a month_year version
