Filtering data as of a particular date

[FarzanaSaleem]

I have a data in an Excel Sheet with employee number and their date of joining. I want to find those employees who have completed 'whole years' as of Dec 31, 2002.

Example:
(Employee Number, Date Of Joining (dd/mm/yyyy))
(1,1/1/1999)
(2,3/1/1999)
(3,1/1/2000)
(4,3/1/2000)
(5,1/1/2001)

Result should be
(1,1/1/1999)
(3,1/1/2000)
(5,1/1/2001)

 

[xlbo]

if the number and join date is in 1 cell, you have BIIIIIIG problems

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[FarzanaSaleem]

They are in different cells. I have used ordered pairs whose member1 is in Column A and member2 is in column B.

COLUMN A COLUMN B
1 1/1/1999
2 3/1/1999
3 1/1/2000
4 3/1/2000
5 1/1/2001

 

[xlbo]

phew.
In that case, select all the data and goto Data>Filter>Autofilter
On the date column, use custom
Select Is less then or equal to from the left side dropdown
In the textbox, enter
31/12/2001

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[FarzanaSaleem]

That will return all the rows. I need to find those employees who have finished 'complete years'. For example, an employee completing 3.5 years should not appear, whereas an employee completing EXACTLY 1,2,3 or 5 years should appear.

 

[xlbo]

ok - in that case - in C2, enter
=IF(AND(DAY(B2)=DAY($D$1),MONTH(B2=MONTH($D$1))),"Exact Year","&quot

where D1 contains 31/12/2002

then just filter on "Exact Year" in col C

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244