Sorting Dates in Excel By Month

Question

My boss has given me a job in which he has given me the names of customers and their wives who has bought our products from 2 to 3 regional offices. My boss wants me to format all this data into one sheet and sort it month wise.

Since there is a regular influx in customers my boss wants the name of customer, their wives name and birthdays updated in the regional sheet to come in the master worksheet.

I have tried pivot table by formatting all the data into a master sheet on separate columns but it is not sorting. I am at my wits end. I have tried referencing but that does not automatically update.

Answer 1

I assume that you have the data in tabled form (that is each row is a customer with wife and birthday). Here is what I did to make this work: First format the column with the birthday to be "month day, year" by highlighting your column then selecting format. I kept it as a date format, but the important thing is to get the month out in front of the formatting.

Then in the column next to it, use the following command:

=TEXT([adjacent cell here],"mm / dd / yyyy")  

Apply this to all cells by selecting the cross in the lower right hand corner of the cell and dragging it down (or double clicking).

Then select this column and perform a custom sort, and select values, and a to z. When it tells you about the "Sort Warning", select the bottom, that says, "Sort numbers and numbers stored as text separately".

Be sure to expand the sort to all cells to keep the information together.

Now that you see these steps, you could then record a macro (using the macro recorder/developer) that does these steps for you to populate future tables. Before you stop the macro recording, you just delete the column you created.

The takeaway from this is that converting to TEXT opens up the custom sort to allow you to sort by month vice chronologically.