Australian PC World - Sorting Excel dates

Sorting Excel dates

Q Can you help me with a question about a function in Excel 97? I would like to sort dates (in this case birth dates) in a spreadsheet containing details about 1200 candidates for recruitment. In what format do I enter these dates so that I can sort them in ascending or descending order? The only success I have had so far is to type them in, beginning with the year, eg 960731 for 31st July 1996.
I hope you can solve this problem for me.
- Trisha Dixon

A Excel supports dates entered in most of the usual formats, such as 31/07/96, 31/7/96 and 31-July-96. You can see some of Excel's built-in display formats by choosing Format--Cells--Number, and selecting Date from the Category box. All valid date formats have underlying serial numbers which Excel uses to perform calculations related to time. Excel for Windows uses the 1900 Date System, in which the days are numbered sequentially from the start of this century. January 1, 1900, for example, has a serial number of 1; December 31, 2078 has a serial number of 65380.
Excel sorts dates by their serial numbers. If you enter dates in an unrecognised format, Excel sorts them as text, which is how your current dates would be sorted. Fortunately, it's possible to convert your dates to serial numbers using a macro, and then sort them.
1. Open your spreadsheet, and, using File--Save As, make a backup copy.
2. Insert a new Visual Basic module using the command Insert--Macro--Module in Excel 95 or Tools--Macro--Visual Basic Editor, then Insert--Module in Excel 97. Create the macro by typing in the code below.

The Convert_date macro, adapted from a Microsoft code sample, converts numbers to serial dates

3. Return to your workbook and select the dates column, ensuring that the first cell contains a date. Choose Format--Cells--Number, select General from the Category box, then click OK.

To change the date format, select all the dates in the Birth Date column and run the Convert_date macro

4. With the dates still selected, run the macro. In Excel 95, choose Tools--Macro; in Excel 97, use Tools--Macro--Macros, then select Convert_date from the dialogue box and click Run. Your dates will be converted to serial dates in the format dd/mm/yy (if dates appear as mm/dd/yy, you will need to adjust Windows' default date settings under Control Panel--Regional Settings). The macro assumes that all your dates are in one column and stops when it detects an empty cell.
5. To sort by date, select all the data in your spreadsheet (this will maintain the integrity of your data during sorting), including the headings of your columns. Choose Data--Sort, select Ascending or Descending sort order, My List has Header Row, and Sort by Birth Date. Click OK.

Use the Sort command to order rows by date

If anything goes wrong, you can undo the sort by choosing Edit--Undo Sort.
To continue adding dates, use the dd/mm/yy format. Once you have used the Convert_date macro, Excel recognises its output as a custom date format. You can apply this to cells by choosing Format--Cells--Number, selecting Custom from the Category box and d/m/yy from the Type box.
- Belinda Taylor

Category: Spreadsheet
Issue: Feb 1998
Pages: 154-156