date sort

[ptebo]

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]

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]

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

HTH

Rocky...

 

[ptebo]

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]

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]

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