Calculate Number of Employees 3

[ksbrace]

Hello,
I'm trying to figure out how many employees I have had at daily intervals. Meaning, let's say I want to look at Jan 3rd and see how many employees I had on that day. Or Feb 15th, same thing. I have 2 columns that are of interest: Date Hired and Termination Date. in format of MM/DD/YYYY. This is for a collection agency and the employee turnover is high, that is why it is somewhat difficult to look. I would love for it to look like this:

Date Num_Emp
9/1/2010 6
9/2/2010 6
9/1/2010 5
9/1/2010 8
......

 

[SkipVought]

Hi,

Check out the COUNTIF() function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

(I think that the dates in your example are wrong.)

Blanks in your ranges add complexity to the solution required.

Using named ranges for your source data.
Assuming that your list of dates is in columnA, then in B4 type:
=SUMPRODUCT(--(HiredDate<=A4),--(HiredDate<>""),--(Termination>A4))+SUMPRODUCT(--(HiredDate<=A4),--(HiredDate<>""),--(Termination=""))

This copes with blanks in the two date columns as follows:
Blank in HiredDate - record not counted
Blank in Termination - still in employment





Gavin

 

[zelgar]

You can use the countifs command. Your statement would look something like this:

=COUNTIFS(A4:A500,"<=2/1/2011",B4:B500,">=2/1/2011")

where,
col A is the Hired Dates of employees
col b is the Termination Dates of employees
2/1/2011 is the date in question

Note, to use a named reference in this formula it must be in the following format:

=COUNTIFS(A4:A500,"<="&DATE,B4:B500,">="&DATE)

where,
DATE = named cell for date or can be replaced by cell reference (e.g., $A$2)

 

[SkipVought]

I would advise against using Date as Named Range, since Date is a BUILT-IN FUNCTION.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}