There HAS to be an easier way to do this than what I'm doing

[anewc1]

But I am not good enough at functions to write one to do this!

I need to find out the number of unique employee IDs with hire dates between a certain date range. Can anyone help? I want to be able to simply paste a function into a summary spreadsheet that will count how many new hires there were on a particular week.

What I have been doing is to use Auto Filter to show the new hires between the dates I want, and then copying those entries to a new spreadsheet and doing an advanced filter on the employee ID numbers to find the number of unique values.

Can anyone recommend an easier way? Thanks in advance!

employee_id last_name first_name hire_date
071001000000000269 8/14/2006
071001000000000269 8/14/2006
071001000000000269 8/14/2006
071001000000000270 8/14/2006
071001000000000270 8/14/2006
071001000000000270 8/14/2006
071001000000000270 8/14/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000234 8/16/2006
071001000000000285 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000288 8/16/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000289 8/18/2006
071001000000000290 8/18/2006
071001000000000290 8/18/2006

 

[Domenic]

If you download and install the free add-in Morefunc.xll, you can use the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=COUNTDIFF(IF(DateRange>=StartDate,IF(DateRange<=EndDate,IF(EmpIDRange<>"",EmpIDRange))),,FALSE)

Hope this helps!

 

[barry houdini]

I see Domenic was too quick for me......

An alternative, if you can't or won't download Morefunc,....

If your employee IDs are in A2:A100 and hire dates in D2:D100 then you can use this formula

=COUNT(1/FREQUENCY(IF((D2:D100 >=F2)*(D2:D100< F2+7),MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1))

confirmed with CTRL+SHIFT+ENTER

where F2 contains your week start date

 

[Barry Katcher]

Data>Filter>Advanced Filter on range $A$4:$B$18 and with Criteria Range of $D$1:$E$2 (with Unique Records Only checked) will give you what you want.

EDIT: Ooops - I forgot the Count. The formula in B19 is:
=SUBTOTAL(2,B5:B18)

Book1
	A	B	C	D	E
1				Hire Date	Hire Date
2				>=8/14/2006	<=8/15/2006
3
4	Employee ID	Hire Date
5	071001000000000269	8/14/2006
6	071001000000000269	8/14/2006
7	071001000000000269	8/14/2006
8	071001000000000270	8/14/2006
9	071001000000000270	8/14/2006
10	071001000000000270	8/14/2006
11	071001000000000270	8/14/2006
12	071001000000000234	8/16/2006
13	071001000000000234	8/16/2006
14	071001000000000234	8/16/2006
15	071001000000000234	8/16/2006
16	071001000000000234	8/16/2006
17	071001000000000234	8/16/2006
18	071001000000000234	8/16/2006
19	Count	14
20
21	Result:
22	Employee ID	Hire Date
23	071001000000000269	8/14/2006
24	071001000000000270	8/14/2006
25	Count	2
Sheet1

 

[anewc1]

=COUNTDIFF(IF(I:I>=7/31/2006,IF(I:I<=8/5/2006,IF(A:A<>"",A:A))),,FALSE)

This is what the formula ended up being when I changed it to reflect my ranges, but it returns a value of FALSE, and I need it to return a number. What am I doing wrong?

Thank you again for all your help.

 

[Barry Katcher]

Ah, the old Formula vs. Menu vs. VBA ploy. Anyone care to contribute the VBA?

 

[barry houdini]

You can't use whole columns, you need to use a specific range like I1:I100, also if you use a date within the formula itself then you need to format differently for it to be recognised as a date. Try

=COUNTDIFF(IF(I1:I100>="7/31/2006"+0,IF(I1:I100<="8/5/2006"+0,IF(A1:A100<>"",A1:A100))),,FALSE)

confirmed with CTRL+SHIFT+ENTER

 

[anewc1]

=COUNT(1/FREQUENCY(IF((I2:I651>=7/31/2006)*(I2:I651<7/31/2006+7),MATCH(A2:A651,A2:A651,0)),ROW(A2:A651)-ROW(A2)+1))

This one didn't work either, it returned a value of zero, and the value should have returned 16. Argh!

What am I doing wrong?

 

[anewc1]

YES!!!

This worked:

=COUNTDIFF(IF($I$2:$I$651>="7/31/2006"+0,IF($I$2:$I$651<="8/5/2006"+0,IF($A$2:$A$651<>"",$A$2:$A$651))),,FALSE)

I just have to remember to use CTRL+SHIFT+ENTER or it comes back with FALSE.

I can't thank you all enough!!