Forum Home
Press F1
 
Thread ID: 31638 2003-03-27 23:51:00 Sorting Dates in Excel Muzz (1286) Press F1
Post ID Timestamp Content User
131487 2003-03-27 23:51:00 I have a large list of names in an Excel Spreadsheet and included with these is these persons dates of birth.
I want to sort this list to give me ascending birthdates - without the year.
ie I want to be able to contact these persons on their birthdates each year.
The sort would therefore need to be done day/month. The 'sort by date' feature in Excel takes into account the year which I don't want.
Can anyone help with a solution?? 		Muzz (1286)
131488 2003-03-28 00:28:00 Try formatting the cells first as Text. Then enter the dates. Then carry out the sort.

Oxie 		Oxie (1318)
131489 2003-03-28 00:34:00 You could put the dates into 3 columns, instead of one. But thats a bit messy. wotz (335)
131490 2003-03-28 00:55:00 In an adjacent column enter the formula =MONTH(datecell)
copy this formula down to the bottom of your list then sort on this column.
Another column could be added with =DAY(datecell)
and sort by month column then day column.

HTH 		Russell D (18)
131491 2003-03-28 02:03:00 In my copy of Excel 97 dates without the year can be sorted

.Format the column as Date , choose the form 4-Mar

Enter dates in that form

Sort in the normal way

Al.W 		Al.W (797)
131492 2003-03-28 02:43:00 You shouild find the following works pretty good.

1. Give your list of birth dates a name (eg. list), using insert - name from the menu.

2. Enter the following array formula in the column where you want the sorted dates
{=small(date(0,month(list),day(list)),row(1:n))}
here n is the number of entries on your list, use =count(list) to find this if you don't want to do it manually.

3. This should give you the sorted dates that you want. Format them to day and month only if you want to omit the year (actually that formula effectively puts all the months and days into 1900 and then sorts them i.e. it puts them all into the same year.)

4. If you're not familiar with array formulas then post back and I'll explain. They're simple enough. 		rugila (214)
131493 2003-03-28 03:16:00 Hi Muzz, think you will need two columns one with the full birth date and one with day and month.

Can't get Excel to do it but Works 4 or 4.5 does, see below.

28 January 1945 28/01
11 March 1967 11/03
3 December 1968 3/12
28 November 1950 28/11

28 January 1945 37649
11 March 1967 37691
26 November 1933 37951
28 November 1950 37953
3 December 1968 37958


Les3 		leshibbard (873)
131494 2003-03-28 03:52:00 Muzz,
To simplify a bit, specially if you don't want to use arrays and automatic sorts, do the following:

A B C
1 Person Birthdate Sort date
2 Saddam 2/7/1944 =date(50,month(B2),day(B2))
3 Dubya 8/11/1951 (fill down)
4 Billy T 1/1/Prehistoric ( fill down)

Then sort on the C column. This will give you what you want, with dear old Billy at the top where he no doubt belongs.

Yes, Excel does it all fine. You don't have to use anything else. 		rugila (214)
131495 2003-03-28 04:05:00 That one wasn't formatted that good.
Maybe this will look better.

A B C
1 Person Birthdate Sort date
2 Saddam 2/7/1944 =date(50,month(B2),day(B2))
3 Dubya 8/11/1951 (fill down)
4 Billy T 1/1/Prehistoric (fill down)

Then sort on the C column. 		rugila (214)
131496 2003-04-06 09:11:00 rugila

Many thanks for your response to my problem. I have followed your suggestion but it will not sort the data as suggested. Any more suggestions???? 		Muzz (1286)
1 2