date sort

ptebo · Apr 30th, 2002, 12:19 PM

We have a spreadsheet that contains employees anniversary dates for each month. We need to sort the list by the day the employee started work. Is there a way to sort by the specific anniversary day (i.e. all of the employees hired on the 1st of the month, then the 2nd, etc. regardless of the year they were hired)instead of the numeric equivalent of the anniversary date which results in the dates being sorted first by the year?

IML · Apr 30th, 2002, 12:24 PM

You could add a column with the formula
=TEXT(A1,"m-d")
where your dates are col A.

Sort by this new column

good luck

Rocky E · Apr 30th, 2002, 01:15 PM

Close, but "=TEXT(A1,"mm-dd")" will sort better.

HTH

Rocky...

ptebo · Apr 30th, 2002, 01:23 PM

Changing it to text still didn't allow me to do the needed sort. What I ended up doing was using the formula =DAY(A1) (assuming my first date was in cell A1). This formula pulled just the day into a new column. After copying the formula where needed, I was able to sort my list first by the new day column, then by my original date column. This enabled me to display my list in order by the day the employee started and then by the year they started.

Rocky E · Apr 30th, 2002, 05:27 PM

Quote:

On 2002-04-30 11:19, ptebo wrote:
We have a spreadsheet that contains employees anniversary dates for each month. We need to sort the list by the day the employee started work. Is there a way to sort by the specific anniversary day (i.e. all of the employees hired on the 1st of the month, then the 2nd, etc. regardless of the year they were hired)instead of the numeric equivalent of the anniversary date which results in the dates being sorted first by the year?

Wait a minute. Read what you said? Give 3 to 5 real dates picked randomly in the there natural order and then the same group sorted, please. Then re-state your sort order for us, please

This stuff really works, trust me.

Rocky...

[ This Message was edited by: Rocky E on 2002-04-30 16:31 ]

Yogi Anand · Apr 30th, 2002, 05:59 PM

Hi Guys:
Using the text value for cell A2 in an adjacent still keeps the memory of the year while sorting, so if the year has to be neglected, so for a corresponding date in cell A2, I want to put the following formula in cell B2 ...

=MONTH(A2)&"-"&DAY(A2)

then drag down
then sort on column B in ascending order.

Hope This Helps!

N.B.
I checked with IML and RockyE's formulation -- these do work if you coerce their text values to numeric values, say with +0 before sorting.

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 13:29 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 30th, 2002, 12:19 PM	#1
ptebo New Member Join Date: Apr 2002 Posts: 6	We have a spreadsheet that contains employees anniversary dates for each month. We need to sort the list by the day the employee started work. Is there a way to sort by the specific anniversary day (i.e. all of the employees hired on the 1st of the month, then the 2nd, etc. regardless of the year they were hired)instead of the numeric equivalent of the anniversary date which results in the dates being sorted first by the year?

Apr 30th, 2002, 12:24 PM	#2
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	You could add a column with the formula =TEXT(A1,"m-d") where your dates are col A. Sort by this new column good luck

Apr 30th, 2002, 01:15 PM	#3
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Close, but "=TEXT(A1,"mm-dd")" will sort better. HTH Rocky...

Apr 30th, 2002, 01:23 PM	#4
ptebo New Member Join Date: Apr 2002 Posts: 6	Changing it to text still didn't allow me to do the needed sort. What I ended up doing was using the formula =DAY(A1) (assuming my first date was in cell A1). This formula pulled just the day into a new column. After copying the formula where needed, I was able to sort my list first by the new day column, then by my original date column. This enabled me to display my list in order by the day the employee started and then by the year they started.

Apr 30th, 2002, 05:59 PM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Guys: Using the text value for cell A2 in an adjacent still keeps the memory of the year while sorting, so if the year has to be neglected, so for a corresponding date in cell A2, I want to put the following formula in cell B2 ... =MONTH(A2)&"-"&DAY(A2) then drag down then sort on column B in ascending order. Hope This Helps! N.B. I checked with IML and RockyE's formulation -- these do work if you coerce their text values to numeric values, say with +0 before sorting. _________________ Yogi Anand Edit: Deleted inactive website from hardcoded signature [ This Message was edited by: Yogi Anand on 2003-01-19 13:29 ]